Decimal Number in Power Query

Question

Good day,

I have a column of numbers with 2 decimal places including things like 0,50 and 2,50.

I loaded this column into power query because I need the numbers split into 2 columns

for numbers before and after the comma.

Regardless of formatting set to 2 decimal places, it imports the numbers 0,50 and 2,50 as 0,5 and 2,5.

When splitting the number this will give me 5 instead of 50. I need it to be 2 decimal places so when split 0,50 will become 0 and 50 and 2,50 will become 2 and 50.

I cannot seem to figure out how to format it correctly.

Does anyone have advice?

Answer 1

To hopefully answer both of your problems, try the following modification.

• instead of rounding, we will multiply by 100
  • convert result to integer type
• integerPart = result/100
• decimalPart = result mod 100

With regard to the error, as a first step set a type transformation to decimal number using locale, and be sure the locale you pick is one that uses the comma as the decimal.

Here is code for my locale  "en-US":

let

    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Column1", type number}}, "en-US"),

    #"Multiplied Column" = Table.TransformColumns(#"Changed Type with Locale", {{"Column1", each _ * 100, type number}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Multiplied Column",{{"Column1", Int64.Type}}),

    #"Inserted Integer-Division" = Table.AddColumn(#"Changed Type", "integerPart", each Number.IntegerDivide([Column1], 100), Int64.Type),

    #"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "decimalPart", each Number.Mod([Column1], 100), Int64.Type),

    #"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Column1"})

in

    #"Removed Columns"

Answer 2

Excel 365

Ron Rosenfeld's query work just fine, of course.

In case you aim is to split a decimal duration number

into days and hh/mm/ss, with rounded seconds,

here is the Power Query way:

https://www.mediafire.com/file/v4s0j34lmjknbbf/12_29_20.xlsx/file

https://www.mediafire.com/file/b5ratzy8mnn3to8/12_29_20.pdf/file

Answer 3

Power Query imports the actual Value of the cell.  Cell formatting only changes the appearance.

You have several options:

• In Excel, either round the formula that is producing those results, or set "Precision as Displayed" in the Excel Options dialog (but doing the latter results in permanent loss of precision for the entire worksheet).
• In Power Query, Round to two decimals; convert to text; split on the decimal delimiter; and, because of an apparent bug/design flaw in PQ which sometimes adds trailing zero's when you round; trim the text portion of the decimal value to two characters.
  • Note that PQ rounding is different from Excel rounding.  PQ rounds to the nearest even number (as does VBA).  So with PQ, for example, both 12.345 and 12.445 would round to 12.44

Here is some PQ code:

let

    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

    #"Rounded Off" = Table.TransformColumns(Source,{{"Column1", each Number.Round(_, 2), type number}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Rounded Off",{{"Column1", type text}}),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),

    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "decimalPart", each Text.Start([Column1.2],2)),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.2"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Column1.1", Int64.Type}, {"decimalPart", Int64.Type}})

in

    #"Changed Type1"

Answer 4

Thank you very much for your answer. Unfortunately option 1 still rounds ,50 off to ,5 and the same happens in Power Query.

I tried your code also and from this step:

 #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "decimalPart", each Text.Start([Column1.2],2)),

I get a column only displaying 'error' in every box.