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
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
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"?
Thank you for this info. I've a question: if instead of giving, for example, 40-50 range directly, i want to make 10 buckets, and want to create the ranges dynamically, how to do that? Any help is appreciated.
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?
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?
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.
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.
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.
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
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?
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.
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?
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!
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?
@@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
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!
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
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
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
This is awesome! did not know we can do it like this in power query!
That groups from example was a game changer for me! Thank you!
BAM 👊
Extremely useful! You make the best PBI videos! Thanks for all you do!
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
thank you for showing multiple ways to accomplish the same thing. have a great weekend!
This was the best video I watched today. That's saying something. Thank you for this much needed breakdown!!
You're amazing!!! working on a personal project for job apps and your video is a game changer for me so thank you.
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
Waited for this tableau function for a while.Thanks Patrick
This was extremely helpful ! Thank you
Great tutorials! thanks Patrick
Making Bi simple, thank you so much for all your vid's, they're so incredibly helpful!
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"?
Thank you for this info. I've a question: if instead of giving, for example, 40-50 range directly, i want to make 10 buckets, and want to create the ranges dynamically, how to do that? Any help is appreciated.
I love you man! My project is sorted now
I usted the condicional Columba option to group and name the buckets, but this option is effortless thanks for helping me be efficient :)
Last one was really amazing
Ive been using DAX all this time - this is amazing
Beautiful! Very easy to do with Power QUERY
Hi, great video. Is there a way of making buckets based on the final outcome of a measure.
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 sooo briliantt! thank you!
Didn't know the one in Power Query. A Great tip!
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
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?
Thank you so much for the instruction!
I know about group by option in power query .... Good to know this ..
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.
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?
This is fab.
The by example functionality is indistinguishable from magic.
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.
marvelous! And exactly the right Shirt to wear.
Thanks Patrick, great overview. Creating Clusters would also be another one.
Thank you, that really helped me :)
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.
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.
is this feature available in power query excel?
Great video thank you!
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)?
this was amazing!
WOW i didn't know you could do that in PQ, Thanks Patrick its Bananas :)
Damn Patrick, you did it again! You should have seen my face when you entered that first row in PQ lol
Is there any way to create groups with different text values ???
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, ....)
You are a life saver
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?
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.
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.
Thank you! :) You the best!
Does it group words into buckets or just numbers/Ranges..
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?
How do you do this with revenue? Like quartiles. Also, Im not seeing "Add by example etc" old version?
ignore second part
Great energy
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.
How do i do this in the online version of Power BI?
Along the same lines, is it possible to create sets in Power BI? Would end users be able to create their own groups?
Thanks guys
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.
absolute bananas!
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!
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?
This is bananas!
Hi awesome video could you please provide sample file to practice the awesome features
This is awesome!
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?
Just one word fantastic!
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!
What about Conditional Column in PQ?
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?
Can you rename the bins/groups in Power Query e.g. 0 - 1000000 translates to 0 -1M, 1000001 - 2000000 translates to 1M -2M,
You are awesome.
Brilliant!
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
It is wonderful.
=') thanks! this helped me alot.
mindblowing
Its really Bananas!!! Good Info!!!
Please provide , .pbix file
❤️
Practise purpose 🙏
Damnnn man. And I was using DAX all this time 😳
Be my mentor plsssss👏🏻
w00t?! Nice, thanks!
Just one suggestion, if you would like to teach someone new to DAX please slow down on your demo.
just by chance, any one knows how to do this with Looker?
I love u ❤❤❤❤❤
That's BANANAS ..... 🍌🍌
None of this work for mac users
I like 🍌🍌🍌🍌💞 Banana ❤️
Bananas
How about just showing us how instead of talking about all the other ways to do it