Column Grouping in Power BI Desktop October Update

Follow Me

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.

Follow Me