At the end of each month, the Power BI team releases a new update to Power BI Desktop. You can go here to learn what’s new in the latest version.
The October release is out – Version: 2.40.4554.361 64-bit (October 2016), and soon Microsoft will announce their new features. (Short update: The new features were announced four hours after I published this blog post. And there is a Snap-To-Grid preview. Check out the instructions below).
Here are my takes on one of the most coolest features you can find in this update: Grouping of Columns. It is fun to catch a feature before it gets announced.
Let’s demonstrate it on the real data – Clinton’s and Trump’s tweets. In my report I have a column chart with the number of tweets by the hour and candidate.
The hour is represented by the column Hour (CT). When you download the latest Power BI Desktop you will be able to right click on the column in the Axis and select Group.
You can also access the Group feature by right clicking a specific column or bar in the visual (For existing visuals you may need to recreate them, or change their type in order to have the new features available in the UI).
After clicking Group, a new dialog will appear:
Grouping columns to bins
From here you can group the columns into multiple bins. In my vase I want to group the 24 hours in a day into bins of 3 hours each, so I selected 3 in the Bin size, and clicked OK.
The result – The column chart is grouped into 3 hour columns.
You can also find a new field under Hour (CT) with the suffix (bins).
If you right click on the (Bins) field, you can edit the Group settings.
Implicit Calculated Measures
Drilling down into the Data Model (Data view), you will notice a new column with the suffix (Bins). It looks like a calculated column, so I was was trying to find the relevant DAX… But couldn’t find any. So we have “Implicit Calculated Columns” here (similar to the implicit measures that are created when you assign a column in the Data Model to the Values of any visual).
Grouping columns by lists
You can change the group type from Bins to List, as shown in this screenshot.
Then, in the Ungrouped values select the items you wish to group together, and click Group. You can select multiple items using the Shift key and separate items using the CTRL key.
You can rename the groups. For example, when I grouped the values 5,6 & 7, Power BI assign the name 5 & 6 & 7. I renamed it to Early Morning.
After creating the Early Morning group, I assigned the hours 8 to 17 to a new group.
After clicking Group, I renamed the group to Work Hours.
Then I renamed the Other group to Late Hours, and clicked OK.
The result – Early Morning, Late Hours and Work Hours. Hey, but it is not sorted right…
We can go to the Data view and select the new column Hours (CT) (groups).
And from here we can use Sort by Columns to re-define the sort order (but to do it, you will need to create a Lookup table with the values Early Morning, Work Hours, Late Hours and add an index with he correct order).
There are other awesome features in this update. For example, you can Exclude and Include specific values from any standard visual. Ain’t it cool?
Update: Snap to Grid can compete with the Grouping as the coolest feature of October update. So I had to mention it here as well, in case you have missed it in Power BI blog/What’s New article.
Snap To Grid – Huge time saver
This feature helps you to align visuals by snapping them the grid as you move them. The feature is still in preview mode, so you’ll need to turn it on.
Go to File –> Options & Settings –> Options, to enable the Snap to Grid feature:
Click OK and restart Power BI Desktop.
In View tab enable Snap objects to grid, and enable Show gridlines.
Does the grouping work while connected to a SSAS Tabular Cube? I don’t see to have that option when trying to replicate your demo?
Do you refer to live connections? Probably not, as grouping creates an underlying calculated column which is not supported.
Is it possible to add the same element to multiple groups? from you example, Is it possible to add 8 in both ‘Early morning’ group as well as ‘working hours’? I was trying to solve something similar and came across this post..
This is not possible using the grouping feature per se, but you can expand your model and create a new table with the pair columns Element and Group. You can send me a sample data with the challenge at hand, and I will try to help. My email is firstname.lastname@example.org
Hi, I have a problem, when I want to make a new group, not all my lines appear. Can you help me with that?
I recommend that you first reach out to the Power BI community forum to get help. I can only support you on technical items that are directly described in the blog. If you don’t get any help, please contact me.
I have a small query and would like to know if this can be solved using group function.
I have a table with column names and sales.
However there are different name of same person ( e.g : amy sham, Amy_sham, Amy.sham, amy@sham etc)
All this name are of same person but maintained wrong can i group all this in one so that total sum of each person be calculated.
Thanks in advance
I would use Power Query Group by with the fuzzy match https://docs.microsoft.com/en-us/power-query/group-by