Power Query - Normalise columns

Question

How do you normalise all columns by their maximum value?

See the following table which we start with:

The result should be that all columns are divided by their own maximum value, as such:

The Power Query script should:

Apply to all columns, the number of columns is dynamic
The columns should be divided by their own maximum value

Alexis Olson Alexis Olson · Accepted Answer · 2019-01-09T17:01:59

Interesting question! Not too difficult if you are comfortable working with M in the Advanced Editor rather than just relying on the GUI.

let
    Source = <Data Source Here>,
    ToColumns = Table.ToColumns(Source),
    DivideByMax = List.Transform(ToColumns,
                      (Col) => List.Transform(Col, each _ / List.Max(Col))
                  )
    Result = Table.FromColumns(DivideByMax, Table.ColumnNames(Source))
in
    Result

What this does is take the table and transform it into a list of lists (columns). Then it transforms each list/column by dividing each element of that list by the max of that list/column. Finally, it transforms the list of lists back into a table using the column names from the first step.

Power Query - Normalise columns

1 Answers