Standardise Columns in Power Query not DAX

[raymond]

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 processing.

I tried to do it with lists in order to get the min and max values but since I have maybe 50 columns I would need to create a lot of lists in order to get the desired result. The effort seems unreasonably high for something so simple 😕

Appreciate any ideas on that 🙂

[raymond]

@mahoneypat Thanks man, appreciate that. Unfortunately I am looking for a way to do that during data prep with power query m

[mahoneypat]

I updated my post with the M approach too.

Regards,

Pat

[raymond]

@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}})

[raymond]

Hello everyone,

thank you guys. 

I think I cannot upload a file. I will make a screenshot:

In SPSS o EXCEL you have a function to standardize values. Basically, for each value in A you have to substract the mean of the entire column A:A and divide the result by the standard deviation of the entire column A:A.

The result helps me to compare different scales e.g. price and purchase power index.

[mahoneypat · Accepted solution]

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

[mussaenda]

Hi @raymond,

May we know what you mean standardise columns?

[AlB]

Hi @raymond 

Can you show with an exmaple what exactl you mean by standarise a column? Can you also provide some sample data that resembles your real scenario so that we run some tests?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers