Forum Discussion
Creating groups based on numbers
I'd like to create groups based on numbers (i.e. if column X is 0-9, returns "0-9 members", if column S is 10-24, returns "10-24 members", etc.). If I use the grouping or list function, it won't let me use <=> and lists the category as every number within that group, makes any visualization outrageously messy, and won't accommodate updated data within the number spans unless it is one of the exact numbers that has already been used. I can only seem to use one IF function in a column - is that correct? Can anyone suggest a work-around?
Thank you!
Select the table/query in question. Hit "Edit Queries".
In there, you have option to add blank query. Do so and go to Advanced Editor.
Paste in "M" as below (you can adjust range as needed or add more arugments).
(vals) => let ValBucket = { {(x)=>x<10, "0-9 members"}, {(x)=>x<25, "10-24 members"}, {(x)=>x<50, "25-49 members"}, {(x)=>true, "50+ members"} }, Result = List.First(List.Select(ValBucket, each _{0}(vals))){1} in ResultOnce confirmed, rename the query to something that makes sense (Ex. BucketFunc).
Now, still in Query Editor, go back to your table where you want to add custom buckets/bands.
In "Add column" ribbon tool, select "Invoke Custom Function".
Choose a name for the column and select "BucketFunc" you just created. "vals" should be column where the values you want to band belongs.
Result should look like below.
aksl,
You could also use DAX to add a calculated column.
Group Name = SWITCH ( TRUE (), Table1[column X] >= 0 && Table1[column X] <= 9, "0-9 members", Table1[column X] >= 10 && Table1[column X] <= 24, "10-24 members", "other" )
14 Replies
aksl,
You could also use DAX to add a calculated column.
Group Name = SWITCH ( TRUE (), Table1[column X] >= 0 && Table1[column X] <= 9, "0-9 members", Table1[column X] >= 10 && Table1[column X] <= 24, "10-24 members", "other" )You are a Star * * * * * * * You saved my life. Thanks alot
v-chuncz-msft I have some negative values also in my dataset but the switch statment is catching those negative values, and these are by default been thrown into other values, how can I group negative values in 0 to 20 statment??
@v-chuncz-msft Also whenever I try to show this in my matrix field it never works, do you think you can help me create the below:
groupings in matrix table, that initailly I had build in the attached link?
- 0-20 Kl
- 21-49 Kl
- 50-99 Kl
- >100 Kl
I'd create Custom Bucket/banding function using "M".
You can find detailed example in below link.
https://www.excelguru.ca/blog/2016/02/29/creating-a-banding-function-in-power-query/
While example pertains to PowerQuery in Excel, same principle applies to PowerBI.
Thanks for your response, Chihiro. I follow the concept, but I still don't see that PowerBI has the functionality to execute this. Am I missing something?
I could do it in Excel, but I am trying to create something that is dynamic, so would like to be able to do it in BI if possible.
Select the table/query in question. Hit "Edit Queries".
In there, you have option to add blank query. Do so and go to Advanced Editor.
Paste in "M" as below (you can adjust range as needed or add more arugments).
(vals) => let ValBucket = { {(x)=>x<10, "0-9 members"}, {(x)=>x<25, "10-24 members"}, {(x)=>x<50, "25-49 members"}, {(x)=>true, "50+ members"} }, Result = List.First(List.Select(ValBucket, each _{0}(vals))){1} in ResultOnce confirmed, rename the query to something that makes sense (Ex. BucketFunc).
Now, still in Query Editor, go back to your table where you want to add custom buckets/bands.
In "Add column" ribbon tool, select "Invoke Custom Function".
Choose a name for the column and select "BucketFunc" you just created. "vals" should be column where the values you want to band belongs.
Result should look like below.
Its as easy as grouping in pivot table. Refer to below tutorial
https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning
