Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Decimal to Text

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

Need Help

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        4 years ago

        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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.

  • angai97's avatar
    angai97
    New Member
    3 years ago

    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. 

     

  • COWA's avatar
    COWA
    Frequent Visitor
    3 years ago

    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.