cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
muratorin
Frequent Visitor

dataflow sqlserver number format error

i am trying to create a dataflow from a sql server table through on premises gateway to a CDS table.

 

everything works if a table has text data only but if I have a numeric field the import is not successful. an example of error from CSV file is:

 

}","{""error"":{""code"":""0x80044330"",""message"":""A validation error occurred for crf40_fielddashboard3.crf40_cc_field_perc. The value 2.29708417551787E+16 of type System.Double is outside the valid range(0 to 1000000000).""}}"

 

seems that there is a number format issue as the value of this record is 0,229 (italian format with comma separating decimals). i tried to fix using transformations but seems the root cause is on the data import. anyone knows how to solve it?

1 ACCEPTED SOLUTION

Accepted Solutions
muratorin
Frequent Visitor

solved! 

 

Root cause was that I designed a flow AND creating the data table. this had nothing to do with international settings 😞

The table generated was creating, for the numeric fields, a 'floating point number' field (sorry that my interface is italian but it's locked by my organization) with minimum value 0 and maximum 10.000.000.000:

 

2021-03-01 12_13_27-Power Apps.png

this was blocking the import. instead, i deleted all these columns and replaced with 'decimal' fields:

 

2021-03-01 12_14_05-.png

 

and it worked! 🙂

 

Now, the question are:

- why the automatic table generator creates datatypes that are not working for the dataflow with arbitrary limits? 

- why having this error message that made me losing one weekend and led to wrong path? I had to be really creative to find another root cause..

 

And as an old style (C++/Java) programmer, i still don't understand the difference between those two datatypes...

 

https://docs.microsoft.com/en-us/powerapps/maker/data-platform/types-of-fields#using-the-right-type-...

 

"When choosing the correct type of number column to use, the choice to use a Whole Number or Currency type should be straightforward. The choice between using Floating Point or Decimal numbers requires more thought.

Decimal numbers are stored in the database exactly as specified. Floating point numbers store an extremely close approximation of the value. Why choose extremely close approximation when you can have the exact value? The answer is that you get different system performance.

Use decimals when you need to provide reports that require very accurate calculations, or if you typically use queries that look for values that are equal or not equal to another value.

Use floating point numbers when you store data that represents fractions or values that you will typically query comparing to another value using greater than or less than operators. In most cases, the difference between decimal and float isn’t noticeable. Unless you require the most accurate possible calculations, floating point numbers should work for you."

 

Anyway, thanks for your help!

View solution in original post

10 REPLIES 10
dpoggemann
Super User
Super User

HI @muratorin ,

Did you validate the definition of your field in the Dataverse?  You should make sure that you have a Decimal Number type defined for the field you are importing into.  Also a link that might be helpful if you don't have (https://docs.microsoft.com/en-us/powerapps/maker/data-platform/add-data-power-query

 

Hope this helps.


Thanks,

 

Drew

Thanks for your reply. I followed all the steps in the link you sent. However is not clear to me what is the validation you mentioned. Can you explain with an example or a link to an article?

dpoggemann
Super User
Super User

Hi @muratorin,

 

What I mean is the column definition for your data in your Dataverse table should be set as a Decimal Number type.  

See this article for creating custom tables in Dataverse (https://docs.microsoft.com/en-us/powerapps/maker/data-platform/data-platform-create-entity).

 

Thanks much,


Drew

It is decimal, that is the reason why the error occur. The error message means that a source number like 2,3456789 (two with 7 decimals) is seen as 23456789 and with more decimals it overflows. Seems that the international formats are not understood. 

dpoggemann
Super User
Super User

HI @muratorin ,

 

Some thoughts here...

1.  In your PowerQuery configuration, do you have your regional settings set to Italian?  

Query-Options-Window.jpg

2. Make sure your formats are setup correctly in the Dataverse environment as well.   

Screen Shot 2021-02-28 at 6.37.32 AM.png

 

Hope this helps, sorry if not...

 

Thanks,


Drew

I cannot find the screen in point 1, how can i reach it? I strongly suppose it's the issue..

dpoggemann
Super User
Super User

I just setup a dataflow for testing this and went into the Project Options (in Options from menu) and you will see the Locale.  

Screen Shot 2021-02-28 at 7.07.20 AM.png

Please let me know if this helps.  


Thanks,

Drew

 

Hi Drew

 

unfortunately it's already in italian 😞

 

2021-02-28 16_05_46-Window.png

 

i've also checked the system settings, it's italian as well..

 

2021-02-28 16_08_29-Window.png

 

dpoggemann
Super User
Super User

Bummer.  

 

Can you verify the integration user you are utilizing, personal options, to verify that that user is setup in Power Apps as Italian as well?  This is the last thing I can thing of to check...

https://docs.microsoft.com/en-us/powerapps/user/set-personal-options 

 

Thanks,


Drew

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,112)