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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KeithHWU
Frequent Visitor

Trying to Scale/Normalize a value column from 1 to 100 based on min/max value in column

Hi, 

 

I have a flat file csv. One column is total score (whole number). This value can be between 0 to 30 for each row (customer). I want to be able to create a custom column in Power Query that calculates the value of that score betwee 1 and 100. 

 

In Excel I would have tried something like =100*<first_score_cell>/MAX($<first_score_cell>$:$<last_score_cell>$)

 

Can I do something similar in M before loading it to power BI? 

My motivation for wanting to do this in Power Query rathe than DAX is because I want to save the M and apply this function to the same updated file each month. 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

FYI that a DAX calculated column would also work and recalculate with each refresh too.  But below is some M code that demonstrates how to do your calculation in the query.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKyUIrViVZyAjFNwExnENNAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Score", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Normalized Score", each [Score]/List.Max(#"Changed Type"[Score])*100),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Normalized Score", type number}})
in
    #"Changed Type1"

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

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

3 REPLIES 3
mahoneypat
Microsoft Employee
Microsoft Employee

FYI that a DAX calculated column would also work and recalculate with each refresh too.  But below is some M code that demonstrates how to do your calculation in the query.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKyUIrViVZyAjFNwExnENNAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Score", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Normalized Score", each [Score]/List.Max(#"Changed Type"[Score])*100),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Normalized Score", type number}})
in
    #"Changed Type1"

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

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


Hello

as it would be with a calculated column in DAX

Thanks Pat, I couldnt copy your full code as I have a lot of transformations going on already but I got there from what you included. So thank you. 

Once I knew that list.max was the right function the bit that was causing me issue was not knowing that I needed to convert my number to Int64 and back again. 

Much appreciated. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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