excelroundingpowerquerydecimalformat

Standardise decimal places in Power query

Pretty simple question but essentially I have a comparison where I want to return the modal value in a column. In some cases, I'm getting 0.50, and 0.5. if both forms of 0.5 are equally present. I suspect this is because of the difference in the decimals (though it may not be).

Regardless it's not obvious how in excel to get all values to be rounded to the same number of decimal places.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}}),
    #"Inserted Rounding" = Table.AddColumn(#"Changed Type", "Round", each Number.Round([Column1], 2), type number)
in
    #"Inserted Rounding"

Rounding only works when a decimal is present.

Essentially what I wish to achieve:

enter image description here

Solution

  • Power Query does not have an option to set a numberformat for a column.

    Setting the data type to Currency.Type may result in a two decimal display (with four decimal precision), but there are instances where more decimals might be displayed. Combining Rounding and Currency.Type would probably always result in a two decimal display (at least it seems to here).

    If you want to be certain all values are displayed with two decimals, you will need to use a text formatted column, but the values would no longer be numeric. You would also have to use this method if you want a different fixed number of decimals.

    = Table.TransformColumns(Source, {"Column1", each Number.ToText(_,"0.00")})