Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
raymond
Post Patron
Post Patron

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 🙂

1 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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

7 REPLIES 7
raymond
Post Patron
Post Patron

@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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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
Post Patron
Post Patron

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.

2020-11-11 13_04_53-standardize - Excel.png

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mussaenda
Super User
Super User

Hi @raymond,

 

May we know what you mean standardise columns?

AlB
Community Champion
Community Champion

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 

SU18_powerbi_badge

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.