Forum Discussion

Bernie6808's avatar
Bernie6808
Frequent Visitor
3 years ago
Solved

Grouping by value

Hi - First post on here, and I'm quite new to Power BI... I am trying to group values into a new column and have created the following 'M' code which seem to work... I just wonder if there is a more elegant way to acheive the same result?

 

= Table.AddColumn(#"Removed Columns3", "Application Bucket", each if [Application Income] < 4999 then "Under 5K"
else
if [Application Income] < 10000 then "5K - 10K"
else
if [Application Income] < 50000 then "10K - 50K"
else
if [Application Income] < 100000 then "50K - 100K"
else
if [Application Income] < 500000 then "100K - 500K"
else
if [Application Income] < 1000000 then "500K - 1M"
else
if [Application Income] < 3000000 then "1M - 3M"
else "3M +")

 

Need Help
Tips and Tricks
  • jbwtp's avatar
    jbwtp
    3 years ago

    Hi Bernie6808,

     

    Alternatively (please note slight change in how the rules are formulated):

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwMFCK1YlWMjRAYQGZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Application Income" = _t]),
    
    rules = {
                {0, "Under 5K"},
                {5000, "5K - 10K"},
                {10000, "10K - 50K"},
                {50000, "50K - 100K"},
                {100000, "100K - 500K"}
    },
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Application Income", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Application Bucket", (x)=>List.First(List.Select(rules, each _{0} >= x[Application Income])))
in
    #"Added Custom"

    Cheers,

    John

4 Replies

    • jbwtp's avatar
      jbwtp
      Memorable Member
      3 years ago

      Hi Bernie6808,

       

      Alternatively (please note slight change in how the rules are formulated):

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUwMFCK1YlWMjRAYQGZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Application Income" = _t]),
    
    rules = {
                {0, "Under 5K"},
                {5000, "5K - 10K"},
                {10000, "10K - 50K"},
                {50000, "50K - 100K"},
                {100000, "100K - 500K"}
    },
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Application Income", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Application Bucket", (x)=>List.First(List.Select(rules, each _{0} >= x[Application Income])))
in
    #"Added Custom"

      Cheers,

      John

    • Bernie6808's avatar
      Bernie6808
      Frequent Visitor
      3 years ago

      Thanks very much for your reply - I will try this and get back to you!