Create Buckets or Groups with Power Query in Power BI
Vložit
- čas přidán 7. 07. 2024
- There are different ways you can create buckets or groups for your data in Power BI. Patrick shows you how this can be done with Power Query along with a few other options.
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #PowerQuery #GuyInACube - Věda a technologie
Awesome. I know the 'column from example' feature is powerful and used it in quite a few cases. This however is one of those cases that you hear of and think 'hey, that's awesome'. Not the first thing one would expect. Yet one to remember! Thanks, Rick
Extremely useful! You make the best PBI videos! Thanks for all you do!
This is sooo briliantt! thank you!
This is awesome! did not know we can do it like this in power query!
thank you for showing multiple ways to accomplish the same thing. have a great weekend!
Waited for this tableau function for a while.Thanks Patrick
Making Bi simple, thank you so much for all your vid's, they're so incredibly helpful!
This was the best video I watched today. That's saying something. Thank you for this much needed breakdown!!
boy do i love this channel i have found everything i have ever needed looking. Anytime its something i havent done before im like guy in the cube probably has a video for it and sure enough
That groups from example was a game changer for me! Thank you!
BAM 👊
Groups/Bins created in the UI end up as calculated columns in the model too and should come with the same caveats mentioned for the DAX method
Beautiful! Very easy to do with Power QUERY
This was extremely helpful ! Thank you
Didn't know the one in Power Query. A Great tip!
Thank you so much for the instruction!
Great tutorials! thanks Patrick
marvelous! And exactly the right Shirt to wear.
You're amazing!!! working on a personal project for job apps and your video is a game changer for me so thank you.
Thanks Patrick, great overview. Creating Clusters would also be another one.
I usted the condicional Columba option to group and name the buckets, but this option is effortless thanks for helping me be efficient :)
I love you man! My project is sorted now
Damn Patrick, you did it again! You should have seen my face when you entered that first row in PQ lol
Thank you! :) You the best!
Last one was really amazing
WOW i didn't know you could do that in PQ, Thanks Patrick its Bananas :)
Very powerful, although like dataveld mentioned, caveats remain the same as the switch statement. Ideal scenario from the model performance standpoint is thtat you create buckets at the source.
this was amazing!
absolute bananas!
This is fab.
The by example functionality is indistinguishable from magic.
This is awesome!
Just one word fantastic!
Thank you, that really helped me :)
I know about group by option in power query .... Good to know this ..
Great energy
Awesome! Is there a way of adding a bin as a filter which can link to different tables and work across the tables regardless of which value you link it to?
This is bananas!
Great video, thank you! One question, how come the bar graph based on age groups doesn't adjust when I use slicers/ filters on the same page? Am I missing sth?
You are a life saver
Thanks guys
Grouping in Power Query powerup the base table and removes ambiguity in data present in the column with specific set of values. Grouping solves a lot of problems, be it Numeric or Non-Numeric data type and null values problems too.
Hello Patric, is there also a way to create groups that are overlapping? I have a column with concatenated Channels (string) and would like to build groups based on if a channel is in my string. There can be strings like "Display, SEA" that would belong to the group Display AND to the group SEA. The aim is to see which channel participates in how many Customer Journeys or filter the Journeys based on Channel participation. I originally had each Channel in a different column per Transaction ID ( row). Since Dax only filters by Columns and not by criteria contained in a row I thought I could build a filter by concatenating all Channels of each Transaction ID in one colum (string). Hope this makes sense! Help is much appreciated!
Brilliant!
I'm just wondering... If these things are in there in PQ (and in other languages/products), how are people to get to know them? Are these "tricks" described somewhere in some kind of documentation? Or do we always have to "accidentally discover such things"?
You are awesome.
This is amazing! Thanks so much Patrick!
The problem I'm encountering though is that I am unable to view more than the 1st 999 rows that show up in power query and therefore I'm only able to define one bin/group (I can't scroll any lower than the 999th row, and they all have just one bin). How I create the 2nd,3rd,4th bin.....if the remaining values are in rows beyond the extent of my scroll capability (999 rows)
I'd appreciate any tips or tricks that come my way.
Sure appreciate your time!
It is wonderful.
nice video. any idea how to sort the group column made using PowerBI feature without using specific grouping labels (eg 01 xxx, 02 yyy, 03 ppp, ....)
Hi, great video. Is there a way of making buckets based on the final outcome of a measure.
mindblowing
Thank you. How do we create dynamic bucket? For an example, let’s say we have created only till 80-100 but we need to create 100-120, 121-140. Is it possible based on the age bucketing can be dynamic?
=') thanks! this helped me alot.
Thank you! In your DAX solution, if I have filters applied in the dashboard, they won't affect this column. How can I address this issue?
w00t?! Nice, thanks!
Hi. Is it possible to bind BIN (or ABC/Pareto) dimentions to Parameters exposed to User. In my report there is slicer in which User can change within certain range A/B border value. With Calculated Column it does not work - the formula sees Max of the Range not selected Value.
Along the same lines, is it possible to create sets in Power BI? Would end users be able to create their own groups?
Is it possible to create the groups based on MEASURES (e.g. price ranges, when calculated price is a DAX measure, not a calculated colum)?
Does it group words into buckets or just numbers/Ranges..
is this feature available in power query excel?
Its really Bananas!!! Good Info!!!
Hi awesome video could you please provide sample file to practice the awesome features
I usually go the proper way and create a dimension table in PQ, with a surrogate key. Small impact to fact tables, but full flexibility in the visuals in regards to Show Items with no values. If you do it how Patrick describes, you'll be stuck with only groups that ARE PRESENT in your fact table, i.e. no ability to display empty groups.
Can you provide an example? Still using age,I do not want to have a fact table with all ages but just min and max to use in visuals. I do not want to use calculated columns to blow up my model. Thanks
@@dvonpluto Create a table of age ranges with names, min and max. Then in Power Query, in your fact table after you have a step to calculate the age, add a custom column that returns you the row from the range table, using this code: let currentDays = [age] in Table.SelectRows(LKP_AgingBuckets, each [RangeStart] = currentDays).
After that just expand that new column to extract the ID. Voila, your fact table will only increase by one integer column, little to no change to the model.
I've used buckets/bins for my dates however when I refresh the data they don't update to include this new information. Does anyone know where I could be going wrong?
How do we make the Groups sort in the correct order instead of the default order? Normally there is an index column to sort by.
Is there any way to create groups with different text values ???
Pls I tried the column for examples; age group solution but it didn't work and I don't know why. Pls I need your advice. Thanks.
I did this but I'm not seeing the groups, I'm getting a Count! I've tried changing the aggregation to various things but nothing gives me a list of my groups. What am I doing wrong?
It might be overkill, but do you think a dimension table for age and a realtionship with the fact table, will make the report faster?
Your thoughts?
it would be blazing fast. love it.
What happened to the Microsoft Histogram visual? Trying to build a report but it is not longer available for download. This is a key visual, why is it gone? Is it coming back? Alternatives?
How would you set the grouping column(s) used by the aggregates in visualizations?
Would also like to know.
I would also like to know.. Plz share Patrick
What about Conditional Column in PQ?
How do i do this in the online version of Power BI?
Damnnn man. And I was using DAX all this time 😳
How do you do this with revenue? Like quartiles. Also, Im not seeing "Add by example etc" old version?
ignore second part
Hi Patrick, how can create bucket in power query with with difference is not same such as0- 5 ,6-15,15- 25,25- 40
You can try it first exactly how Patrick showed it and continue typing some examples, if this does not work you can still use a conditional column or a custom column with a nested if
@@cristianprifti Or you can use my example and just make the min, max whatever you like.
Can you rename the bins/groups in Power Query e.g. 0 - 1000000 translates to 0 -1M, 1000001 - 2000000 translates to 1M -2M,
Hi, I have a little challenge. I need to group text values on power query. Say for instance, I want to group status Managers and Assist Managers into management level, Director into Executive level and so on. I want it done on power query. Thanks
Hi, i need to do something like this. Were you able to do this?
Hey Guys, I have just found you and loving the vids. I have a unique challenge that is quite complicated. Can I send an email with an explanation and the file for you to review? thanks
Hi Patrick/Adam - can you create a video how to create checkbox table to filter selected data from table
That is a built in function?? Or am I misunderstanding you? It's a slicer. Checkbox is usually one of the options...
@@pabeader1941 actually I have a requirement like need to filter from table like if I selected 3 users in the table it should filter that 3 users data in remaining visuals of the page
@@srinug85 Isn't that a slicer? Just put the users in a slicer and then you can select as you want. Or just select from the filter pane.
using live connection these options are grayed out, what next?
Please provide , .pbix file
❤️
Practise purpose 🙏
I love u ❤❤❤❤❤
just by chance, any one knows how to do this with Looker?
That's BANANAS ..... 🍌🍌
Just one suggestion, if you would like to teach someone new to DAX please slow down on your demo.
Bananas
I like 🍌🍌🍌🍌💞 Banana ❤️
None of this work for mac users
How about just showing us how instead of talking about all the other ways to do it