Forum Discussion
Most Efficient Way to Create Buckets?
Hi,
I'd like to bucket order size by units (1-5, 6-10, 11-20, 21-30, 30+) and use it as a slicer or field in report visuals. My fact table contains more than 17 million rows, so I want to do this as efficiently as possible. Is it best to create a conditional column in Power Query during the ETL stage, or add a column using DAX?
Or is there another alternative that I'm missing? Thanks!
15 Replies
Dave1mo1 Best way to define the bucket is by using "Bins and Grouping" feature of PBI. Attached is the official blog for your reference:
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning
Hi Tahreem,
Thanks so much for the link. I did try using the "bins" feature, but I can't figure out how to change the name of the bin. If I say I want a bin size of "5" for units, it gives me bin names of "0," 5," "10," etc. I want the bin name to reflect the range of 1-5, 6-10, etc.
Bump - Any other ideas? Thanks!
Thank you Tahreem24
Hi, Dave1mo1
When your conditional columns only depend on other columns in the current table, it is usually better to perform in the ETL phase.
If your calculated columns depend on fields from other tables, you can create DAX calculated columns.
SQL BI has an article dedicated to this, and I believe it can help you make the right decision:Comparing DAX calculated columns with Power Query computed columns - SQLBI
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Jian,
That is helpful - thanks. I am trying to create buckets based on an aggregation. My data comes in at an "order line" granuality, but I'd like to aggregate at the order level to show total units for each unit. Then I'd like to group the total units for each order into buckets (1-5, 6-10, etc.).
I can write the DAX measure to give me total units for an order easily, but then that result doesn't "live" anywhere for me to bucket. When I tried to aggregate in the ETL stage by duplicating the query and using "Group By" in the second query, it took a very long time (I have ~17m records in this table, so it's somewhat understandable).
I'm not sure how I can both aggregate at a higher granularity than my fact table and bucket that aggregation in an efficient way. Do you have any other ideas?
Dave1mo1 ,
You can use 2 table approach
1. 1st is your original table where you want to have the bucket information
2. Create a new table that has the Units grouped by Order level. This table as of me will have only 2 columns (Order, Units).
3. To the table created in step 2, add a calculated column to define the buckets as per your requirement using conditional column option
4. Using Merge option in Power Query editor, Merge these two tables and get the Bucket info alone to the 1st table.
5. This should give you a table with details at Order line number while also having the bucket information for the Order.
With your volume of records, can't be sure about the time it could take. But it is worth a try.
Although I would prefer doing this same approach in a much higher level like writing this in a SQL statement and pulling in only the output rather than doing this inside Power BI
Regards,
Hi,
You may try using measures to solve this problem. Please see the attached files.
