Grouping by value

[Bernie6808]

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 +")

[Bernie6808]

Thanks John, I'll give this a go.

[ImkeF]

Hi @Bernie6808 ,
good that you asked - there is indeed a georgeous method that you will probably like. It also allows you to keep your ranges in a nice table apart from the code that you can adjust much simpler if needed:
VLOOKUP-True Equivalent in Power Query with Dynamic Tiers - YouTube

[Bernie6808]

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

[jbwtp · Accepted solution]

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