Answered

dataflow sqlserver number format error

(0) Share

Report

Posted on by muratorin

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?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report

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

Was this reply helpful? Yes No
muratorin 18 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
muratorin 18 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

HI @muratorin ,

Some thoughts here...
1. In your PowerQuery configuration, do you have your regional settings set to Italian?
2. Make sure your formats are setup correctly in the Dataverse environment as well.

Hope this helps, sorry if not...

Thanks,

Drew

Was this reply helpful? Yes No
muratorin 18 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

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

Thanks,
Drew

Was this reply helpful? Yes No
muratorin 18 on at

Like (0)

Report

Hi Drew

unfortunately it's already in italian 😞

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

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

muratorin 18 on at

Like (0)

Report

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:

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

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-of-number

"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!

Was this reply helpful? Yes No