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

[KeithHWU]

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. 

[mahoneypat · Accepted solution]

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

[Syndicate_Admin]

Hello

as it would be with a calculated column in DAX

[KeithHWU]

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.