Forum Discussion

raymond's avatar
raymond
Post Patron
5 years ago
Solved

Standardise Columns in Power Query not DAX

Hi Community,   is there a way to standardise entire columns with M Power Query? I know it can be done in DAX but I would like to standardise a large number of columns and make that part of the pre...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    You can do this in the query editor or with a DAX calculated column.  Here is how to do it with a DAX column.

     

    Standardized = (Table[Value] - STDEVX.P(Table, Table[Value]))/AVERAGEX(Table, Table[Value])

     

    In the query editor, you can do it with a step like below.  You can transform the column in place (not add a new one) by doing a simple math transform (e.g., +1) and then modify the code as below.  The "_" is the reference to the value on that row and the #"Changed Type"[Value] refers to the previous step name and the column with all the values.  You may have to change it for the name of your previous step and number column(s).

     

    = Table.TransformColumns(#"Changed Type", {{"Value", each (_ - List.StandardDeviation(#"Changed Type"[Value]))/List.Average(#"Changed Type"[Value]), type number}})

     

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

I updated my post with the M approach too.

Regards,

Pat

raymond's avatar
raymond
Post Patron
5 years ago

mahoneypat : WOW what a great formula. This worked!

 

One little thing, the mean and SD in the equation are switched, it should be like that:

 

= Table.TransformColumns(#"Changed Type", {{"Value", each (_ - List.Average(#"Changed Type"[Value]))/List.StandardDeviation(#"Changed Type"[Value]), type number}})