cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KSven
Advocate V
Advocate V

Dataflow | Decimal numbers with scientific notation

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:

 

KSven_1-1611780626946.png

 

 

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

9 REPLIES 9
v-jefferni
Community Support
Community Support

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.

v-jefferni_0-1611830612399.png

 

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.

Hi @v-jefferni ,

 

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

 

BR,

Sven

Tristan21
Helper I
Helper I

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.

DV Dataflow.jpg

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.

Dataflow DecimalTypeToFrench.jpg

 

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

Hi @Tristan21 ,

 

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

 

KSven_0-1631785979305.png

 

BR,

Sven

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. 

Tristan21_0-1631788761437.png

 

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

 

KSven_0-1631790321310.png

Ticket ID: #2101280050001814

 

Maybe you have a different dataverse version..

 

BR,

Sven

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

Kind regards

@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. 😞

Glad to be a part of your learning process 😉

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (3,150)