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 ...
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

  • 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!