Grouping Numbers in the Query Editor

Power BI
1

So I know I can just select the column in Power BI and create groups but I really want to do it on the query editor side so that it shows up in the logic. Or is there a Switch True that would be better for this? When dealing with ranges I have not had much lunch.

Using the below table I want to be able to say:

  • If the Code falls between 6111 and 6759 then call this 1 - Revenue. ’
  • If the code falls between 7002 and 7050 then call this 3 - Salary Expenses.
  • If the code falls between 7120 and 7720 then call this 4 - Operational Expenses.
  • If the code is 7770 and the Function is 20 then call this 5 - Scholarship Expenses.
  • If the code is between 7770 and 7780 and the Function is 10 then call this 2- Institutional Funded Scholarships.
Name Code Function Object Type
1 - Revenue 6111 - 6759 NA Revenue
3 - Salary Expenses 7002 - 7050 NA Expense
4 - Operational Expenses 7120 - 7720 NA Expense
4 - Operational Expenses 7850 - 8100 NA Expense
5 - Scholarship Expenses 7770 - 7770 20 Expense
2 - Institutionally Funded Scholarships 7770 - 7780 10 Revenue
2

First you need to break the code part into two separate columns in the query editor. This is simple and I’m sure you know how to do it.

Then creating logic from there should be relatively straight forward using grouping formula techniques.

Here’s many examples to review.

https://blog.enterprisedna.co/tag/power-bi-dynamic-grouping/

I would always look to do this is DAX. I think it would be pretty fast if you set this first table up correctly.

Sam

3

Here’s actually a really good example as well from a learning summit session.

Check out around the 20 minute mark for the exact technique.

image
image1142×249 89.6 KB