Decimal to Text

[Anonymous]

Hello everybody,

I am trying to load an Excel-Sheet into Dataverse, with a variety of Datatypes, via Dataflow. But i want to load all off them as plain text. All transformational steps should be happening on a later/second data flow.
But when I load a column, that contains decimal numbers, they are receiving unwanted decimal places. This leads to crashing my second dataflow.

The way powerquery shows preview (and the way it should be)

How the data is stored in Dataverse

For example 291,15 => 291,14999999999998

How to avoid this unwanted transformation?
Thanks all

[COWA]

Ever find a solution?  I have the same thing.
Reading a a column in Excel that can either have number or text.

Reading in some of the number it just adds decimal places

ex. 2209.2 in Excel becomes 2209.1999999999998

funny thing is the next line is 2209.3 and it becomes 2209.3000000000002

happens again for 2211.2 and 2211.3 in the same way.

But all the other rows are fine.  Bizare.  

[angai97]

Hi Bellidues, 

Did you ever resolve this issue? I am running into the same issue where my excel data sheet will show 4 decimal places, but when I loaded it into PowerBI, it will show as having 9+ decimal places. 

[v-stephen-msft]

Hi @Anonymous ,

Power BI has a data type automatic detection function, it will automatically detect the data type of the data when you import the data. You can turn it off in the options.

If you only want to change the data type to text format, you can modify it in Power Query or in Desktop.

Tips: Power Query detects at most the eighth decimal place. You can refer to the following test.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank You for Your replay,

I had the auto detection allready turned off. In Power Query everything looks fine (in Datatype "Any") But when I run the Dataflow, the decimal places appear in the table out of nowhere.

When I Change the Datatype to "Text", the decimal places appear allready in the Query.

It is drving me nuts, I dont want it to do any transformation, just keep plain text without altering it 

[v-stephen-msft]

Hi @Anonymous ,

Because the data flow is managed in Power BI Service, the automatic detection you turn off is only in Power BI Desktop. You may need to modify the data type.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It is PowerApps Dataflow, so the option for Auto detect should be applicable. I am not working with Power BI in any kind. (Maybe another Subforum would have been more suiteable)

Seems to be the same Problem here.
Power-Query-Power-BI-Problem-with-Decimals 

=>Cant change anything in the Source-Excel
=>Need to import as Text/String
=>Cant Round a String
=>Need to convert on a second Dataflow to Number

Wouldn't have thought this to be so exotic 😞

[Greg_Deckler]

@Anonymous Maybe Number.Round with 2 decimal places?

[Anonymous]

I have seen this formular earlier, but round ist not really working on text? Or do you mean on the secound flow, that is converting the "long text value" into an actual decimal?
How can i include this formula in converting text to rounded decimal?

[Greg_Deckler]

@Anonymous Well, in Power Query the way it is shown is that it is either text or numeric (ABC 123) in header. So, maybe doing an explicit transformation to text would be the answer so that the header shows just ABC. You could potentially do the Round before the transformation to text but it may not be necessary if you explicitly transform it to text.

[Anonymous]

Transforming from "Any" to "text" does not help. But the decimal places appear allready in the Query, instead of only in the data table.

[watkinnc]

Looks like you have some floating point math going on there. You should use a round function on the Excel end of things before making it a text value to make sure that the underlying value is truly a whole number, not just being displayed as a whole number. 

--Nate

[Anonymous]

Sadly I cant really change the way the excel comes in.

It gets dropped off by others, and needs to be processed in power query from there