Column Grouping in Power BI Desktop October Update

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.

screenshot_1

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).

screenshot_17

After clicking Group, a new dialog will appear:

screenshot_2

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.

screenshot_3

The result – The column chart is grouped into 3 hour columns.screenshot_4

You can also find a new field under Hour (CT) with the suffix (bins).

screenshot_5

If you right click on the (Bins) field, you can edit the Group settings.

screenshot_6

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).

screenshot_7

Grouping columns by lists

You can change the group type from Bins to List, as shown in this screenshot.

screenshot_8

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.

screenshot_9

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.screenshot_10

After creating the Early Morning group, I assigned the hours 8 to 17 to a new group.screenshot_11

After clicking Group, I renamed the group to Work Hours.

screenshot_12

Then I renamed the Other group to Late Hours, and clicked OK.

screenshot_13

The result – Early Morning, Late Hours and Work Hours. Hey, but it is not sorted right…screenshot_14

We can go to the Data view and select the new column Hours (CT) (groups).

screenshot_15

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).

screenshot_16

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?

screenshot_17

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:

screenshot_18

Click OK and restart Power BI Desktop.

screenshot_19

In View tab enable Snap objects to grid, and enable Show gridlines.

screenshot_20

2 comments

  1. Kyle Reply

    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?

    Thanks.

    • Gil Raviv Post authorReply

      Do you refer to live connections? Probably not, as grouping creates an underlying calculated column which is not supported.

Leave a Reply