Forum Discussion
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 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 🙂
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
7 Replies
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
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.
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 Thanks man, appreciate that. Unfortunately I am looking for a way to do that during data prep with power query m
I updated my post with the M approach too.
Regards,
Pat
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}})
