none
Excel PowerQuery decimal to text conversion error

    Question

  • Hello,

    I have a problem in Excel 2016/2019 using PowerQuery - Import from Folder. There are some XLSX files exported from an application that should be combined into a single report file. The problem arises when a column with some alphanumeric codes is transformed as text. For codes that are based only on mumbers, a code like this "1.2" or "10.2" will be transformed as "1.199999999999999" or "10.199999999999999".

    Basically any .2 code will be transformed as .199999999999999 but could go crazy and convert 1.1 to 1.1000000000000001.

    Is this a bug?... or there is a way to get the correct value in PowerQuery without modifying the source files because is the way they are exported?

    EDITED: It seems that PowerQuery is reading wrong value, and when converting to text just showing the misread value.

    Kind Regards,

    Lucian


    • Edited by Lucian C Saturday, May 18, 2019 8:04 AM New information added
    Saturday, May 18, 2019 7:25 AM

Answers

  • try this:

    let
        strFilePath = Excel.CurrentWorkbook(){[Name="rngSourceFolder"]}[Content]{0}[Column1]&"\File1.xlsx",
        Source = Excel.Workbook(File.Contents(strFilePath), null, true),
        Sheet1_Sheet = Source{[Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
        TransformCol = Table.TransformColumn(#"Promoted Headers", {{"Values", each try Text.From(Number.Round(Number.From(_),2)) otherwise Text.From(_), type text}})
    in #"TransformCol"

    you can set up how many decimals you want here

    Number.Round(Number.From(_),2)

    • Edited by anthony34 Tuesday, May 21, 2019 6:05 AM
    • Marked as answer by Lucian C Tuesday, May 21, 2019 12:00 PM
    Tuesday, May 21, 2019 6:02 AM

All replies

  • Hi Lucian,

    Are you seeing the problem in the Source step, in the #"Changed Type" step, or both?

    Saturday, May 18, 2019 10:15 PM
  • Hi Colin,

    It seems that it came from Source.

    I've made a simple test. Create an Excel file with the values as seen in the image, then trying to "import from workbook" with PowerQuery using simple transformations:

    let
        strFilePath = Excel.CurrentWorkbook(){[Name="rngSourceFolder"]}[Content]{0}[Column1]&"\File1.xlsx",
        Source = Excel.Workbook(File.Contents(strFilePath), null, true),
        Sheet1_Sheet = Source{[Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Values", type text}})
    in
        #"Changed Type"

    And 1.1 became "1.1000000000000001" and 10.2 became "10.199999999999999".

    If needed, I can attach the test files.

    Kind Regards,

    Lucian

    Sunday, May 19, 2019 6:11 AM
  • The problem is not specific to Power Query, but float numbers. This causes problems in all kinds of software. More info here:

    http://effbot.org/pyfaq/why-are-floating-point-calculations-so-inaccurate.htm

    To solve it, you can round the number down to your desired decimal using Number.Round or any other rounding function such as Number.RoundDown:

    https://docs.microsoft.com/en-us/powerquery-m/number-round

    In your case, this would fix the problem and be able to handle up to 5 decimals:

    let
        strFilePath = Excel.CurrentWorkbook(){[Name="rngSourceFolder"]}[Content]{0}[Column1]&"\File1.xlsx",
        Source = Excel.Workbook(File.Contents(strFilePath), null, true),
        Sheet1_Sheet = Source{[Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
        #"Rounded Numbers" = Table.TransformColumns(#"Promoted Headers", "Values", each Number.Round([Values],5)),
        #"Changed Type" = Table.TransformColumnTypes(#"Rounded Numbers",{{"Values", type text}})
    in
        #"Changed Type"

    It seems after testing, rounding with even 200 decimals will fix the problem and will keep all decimals, if you need much more than 5

    Monday, May 20, 2019 10:18 AM
  • Hello anthony34 and S.Risemann,

    Thank you for your time and suggestions.

    I am sorry I might mislead you because I've omitted a fact. That column is not entirely numeric. That values are in fact text representing some codes like 10.A, 10.B but sometimes that codes are entirely numeric as 10.1 and here disaster comes.

    Unfortunately the conversions from "Currency" will work only for the "real numeric" values but with alpha-numeric will fail.

    S.Risemann, I'm sorry but I can't make your sample work at all, it just throw an error at "Rounded Numbers" step: 

    Expression.Error: We cannot convert the value "Values" to type List.
    Details:
        Value=Values
        Type=Type

    Any other ideeas?

    Kind Regards,

    Lucian

    Monday, May 20, 2019 9:08 PM
  • try this:

    let
        strFilePath = Excel.CurrentWorkbook(){[Name="rngSourceFolder"]}[Content]{0}[Column1]&"\File1.xlsx",
        Source = Excel.Workbook(File.Contents(strFilePath), null, true),
        Sheet1_Sheet = Source{[Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
        TransformCol = Table.TransformColumn(#"Promoted Headers", {{"Values", each try Text.From(Number.Round(Number.From(_),2)) otherwise Text.From(_), type text}})
    in #"TransformCol"

    you can set up how many decimals you want here

    Number.Round(Number.From(_),2)

    • Edited by anthony34 Tuesday, May 21, 2019 6:05 AM
    • Marked as answer by Lucian C Tuesday, May 21, 2019 12:00 PM
    Tuesday, May 21, 2019 6:02 AM
  • Hello anthony34,

    Thank you very much for your message. Indeed, now I have the proper results.

    Kind Regards,

    Lucian

    Tuesday, May 21, 2019 12:00 PM