Data flow refresh issue containing decimal number

[PratikParab]

We are syncing an on-prem view containing an 8 decimal place number. We have mapped a decimal column in a view to a decimal column in Entity. We have some rows which contain number 0.00000100 and it results in sync error

POST https://***.****.dynamics.com/api/data/v9.1/chc_dataflow_v_baseratecodes {"chc_base_rate_desc":"3 MONTH LIBOR EUR","chc_effective_date":"2019-09-26T00:00:00.000Z","chc_int_rate_code":"81","chc_interest_rate":"1E-06","chc_interest_rate_string":"0.00000100"}

Reason: Bad Request, Error code: 0x0, Message: An error occurred while validating input parameters: Microsoft.OData.ODataException: Cannot convert the literal '1E-06' to the expected type 'Edm.Decimal'. ---> System.FormatException: Input string was not in a correct format.\r\n at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)\r\n at System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt)\r\n at System.Convert.ToDecimal(String value, IFormatProvider provider)\r\n at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)\r\n at Microsoft.OData.ODataPayloadValueConverter.ConvertStringValue(String stringValue, Type targetType)\r\n at Microsoft.OData.ODataPayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference)\r\n --- End of inner exception stack trace ---\r\n at Microsoft.OData.ODataPayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference)\r\n at Microsoft.Crm.Extensibility.ODataV4.CrmPrimitivePayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference)\r\n at Microsoft.OData.JsonLight.ODataJsonLightReaderUtils.ConvertValue(Object value, IEdmPrimitiveTypeReference primitiveTypeReference, ODataMessageReaderSettings messageReaderSettings, Boolean validateNullValue, String propertyName, ODataPayloadValueConverter converter)\r\n at Microsoft.OData.JsonLight.ODataJsonLightPropertyAndValueDeserializer.ReadPrimitiveValue(Boolean insideJsonObjectValue, IEdmPrimitiveTypeReference expectedValueTypeReference, Boolean validateNullValue, String propertyName)\r\n at Microsoft.OData.JsonLight.ODataJsonLightPropertyAndValueDeserializer.ReadNonEntityValueImplementation(String payloadTypeName, IEdmTypeReference expectedTypeReference, PropertyAndAnnotationCollector propertyAndAnnotationCollector, CollectionWithoutExpectedTypeValidator collectionValidator, Boolean validateNullValue, Boolean isTopLevelPropertyValue, Boolean insideResourceValue, String propertyName, Nullable`1 isDynamicProperty)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadEntryDataProperty(IODataJsonLightReaderResourceState resourceState, IEdmProperty edmProperty, String propertyTypeName)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadPropertyWithValue(IODataJsonLightReaderResourceState resourceState, String propertyName, Boolean isDeltaResourceSet)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.<>c__DisplayClass9_0.<ReadResourceContent>b__0(PropertyParsingResult propertyParsingResult, String propertyName)\r\n at Microsoft.OData.JsonLight.ODataJsonLightDeserializer.ProcessProperty(PropertyAndAnnotationCollector propertyAndAnnotationCollector, Func`2 readPropertyAnnotationValue, Action`2 handleProperty)\r\n at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadResourceContent(IODataJsonLightReaderResourceState resourceState)\r\n at Microsoft.OData.JsonLight.ODataJsonLightReader.StartReadingResource()\r\n at Microsoft.OData.JsonLight.ODataJsonLightReader.ReadResourceSetItemStart(PropertyAndAnnotationCollector propertyAndAnnotationCollector, SelectedPropertiesNode selectedProperties)\r\n at Microsoft.OData.JsonLight.ODataJsonLightReader.ReadAtStartImplementationSynchronously(PropertyAndAnnotationCollector propertyAndAnnotationCollector)\r\n at Microsoft.OData.ODataReaderCore.ReadImplementation()\r\n at Microsoft.OData.ODataReaderCore.InterceptException[T](Func`1 action)\r\n at System.Web.OData.Formatter.Deserialization.ODataReaderExtensions.ReadResourceOrResourceSet(ODataReader reader)\r\n at System.Web.OData.Formatter.Deserialization.ODataResourceDeserializer.Read(ODataMessageReader messageReader, Type type, ODataDeserializerContext readContext)\r\n at System.Web.OData.Formatter.ODataMediaTypeFormatter.ReadFromStream(Type type, Stream readStream, HttpContent content, IFormatterLogger formatterLogger)

[EricRegnier]

Hi @PratikParab,

The default decimal places in CDS is 6. In make.powerapps.com, navigate to your entity (via Solution or Data menu) and increase the decimal places to 8. Don't forget to save all and publish all customizations.

More info on how to edit fields: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-edit-field-portal

Cheers!

[PratikParab]

Hi @EricRegnier 

I have done all that, increased the decimal places 8, saved and published the customisations and even deployed to to different environment and tenant. Still the same.

Thanks

Pratik Parab

[EricRegnier]

From your error message it looks like Dataflow is trying to set the string "1E-06" to the hc_interest_rate field. Can you try to explicitly convert the value to a decimal in Power Query? Also 1E-06 is 1000000 is that was your expecting to set?

https://docs.microsoft.com/en-us/powerquery-m/number-fromtext

[PratikParab]

Hi @EricRegnier,

If you check the input i have formatted the number in both decimal and string

"chc_interest_rate":"1E-06",

"chc_interest_rate_string":"0.00000100"

When i try to save the decimal "0.00000100" to a decimal field in CDS it formats it as 1E-06, but when i try to save it into a string field it formats as "0.00000100".

So what I think is happening here is the dataflow/power query is formatting the number "0.00000100" to 1E-06 and when it tries to save to CDS it throws the error in my post. It seems CDS is converting my number to scientific notation and intern failing to save it to the Entity.

P.s. 1E-06 is "0.00000100" and not 1000000 

[EricRegnier]

I would open at Microsoft support ticket at https://admin.powerplatform.microsoft.com/support. Cheers

[KSven]

Hi @PratikParab ,

have you ever solved the problem? Just have the same issue...

https://powerusers.microsoft.com/t5/Microsoft-Dataverse/Dataflow-Decimal-numbers-with-scientific-not...

Haven't seen you topic before..

BR,

Sven

[PratikParab · Accepted solution]

Hey @KSven, The issue is resolved on our tenant. They have identified the issue and fixed it on our tenant. Surprised this isn't rolled out across Power Platform. Please raise a ticket with them. 

Also please accept this as solution for future reference.

Regards,

Pratik