Dataflow | Decimal numbers with scientific notation

(0) Share

Report

Posted on by KSven

182

Hi lovely Community,

just have created an Dataflow to save some decimal number with high decimal places.

Already have set up my table to decimal number with maximum of 10 places. Thats fine for me, but with Power Query I get sometimes the following format:

Although Ive data type "Number" in Power Query in Dataflow refresh protocol I get the following warning error message:

Cannot convert the literal '7.0298769771529E-05' to the expected type 'Edm.Decimal'. ---> System.FormatException: Input string was not in a correct format.

Do you know how to fix this? I already have tried to round off to 10 places, but theres still this format.

Thank you!

BR,

Sven

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (9)

Answers (0)

v-jefferni on at

Like (0)

Report

Hi @KSven ,

Would you like to add data to Dataverse table via Power Query? What is the source of data, Excel worksheet?

I made a test with variety formats of columns in the worksheet. Did not throw any error at all, however, only the text format number could be recognized.

So, could you please try to format as Text in the table? Then you could leverage the Value function to convert the text type values to number type values in Power Apps:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-value

If this not work, I would suggest create a ticket in the support page:
https://powerapps.microsoft.com/en-us/support/

Hope this helps.

Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
KSven 182 on at

Like (0)

Report

Hi @v-jefferni ,

thnak you for your help. MS Ticket is running, I will post the solution here 🙂

BR,
Sven

Was this reply helpful? Yes No
Tristan21 69 on at

Like (0)

Report

Hello, apparently I am having the same issue with PQ dataflow while loading from Excel, decimal figure (with"," as decimal separator). Comma decimals are automatically replaced by "." in PQ DataFlow display.
Then when lending into the Dataverse Table expecting decimal with "," it returns the following error: "
"An error occurred while validating input parameters: Microsoft.OData.ODataException: Cannot convert the literal '292,8' to the expected type 'Edm.Int32'. ---> System.FormatException: Input string was not in a correct format."

Adding a transformation step in PQDataflow "applying the local format to French for the comma" doesn't show any effect neither at the PQdisplay nor at the Dataverse input.

Any idea why the country format is lost in between or can't be re-applied with Dataflow? Might also be an environment setting as I am mixing English and French... Thanks and kind regards

Was this reply helpful? Yes No
KSven 182 on at

Like (1)

Report

Hi @Tristan21 ,

please ensure you have set the right project region setting (should be the same like your dataverse):

BR,
Sven

Was this reply helpful? Yes No
Tristan21 69 on at

Like (0)

Report

Thank you for the suggestion, it was not aligned. I changed it from US to French, but it didn't fix the decimal separator.
So I introduced a Date type field, to check the region conversion, and it works perfectly. The date in French format DD/MM/YY from Excel, into DataFlow, in Dataverse, all well displayed.

Was this reply helpful? Yes No
KSven 182 on at

Like (1)

Report

That's strange. Here's the answer I got from support in March 2021:

Ticket ID: #2101280050001814

Maybe you have a different dataverse version..

BR,
Sven

Was this reply helpful? Yes No
Tristan21 69 on at

Like (0)

Report

It's a Teams Dataverse (DV4T). That could explain.
Kind regards

Was this reply helpful? Yes No
Tristan21 69 on at

Like (0)

Report

@KSven All my fault, ... , I should probably delete my comment from the beginning, 😞 shame on me, I was insisting pushing decimal into interger on Dataverse side. 😞

Was this reply helpful? Yes No
KSven 182 on at

Like (0)

Report

Glad to be a part of your learning process 😉

Was this reply helpful? Yes No