Forum Discussion

KeithHWU's avatar
KeithHWU
Frequent Visitor
6 years ago
Solved

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. 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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

    • KeithHWU's avatar
      KeithHWU
      Frequent Visitor
      6 years ago

      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.