Group Bin in Power Query

Scenario:    

This blog will focus on getting you to learn how to calculate group distribution using Power Query in Power BI. This training is a relatively easy one. We’ll provide a dataset containing product and value and our goal will be to design a histogram to better communicate insights on the product base.

Initially imported table model is like below: 

Detailed steps： 

Import the data into Power BI query editor and ensure that your columns are in the required data type.
Now we need to create the value group classification to create our histogram. We are simply binning our value column into the groups.

However, there was no grouping column in the table, so you need to create a value group column. We could add sorting step to avoid columns being out of order after grouping.

Solution 1:

In order to find the value group: first, find out the minimum value of the value group,( such as 22 → 20, 34 → 30), and remove the single digit. You can divide it by 10, and use Number.IntegerDivide to round it. The result is multiplied by 10. Find the minimum value and 9 to get the maximum value. Use Text.Format to merge to avoid errors caused by different data types.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc7LDYAwDAPQXXLuAVqKuPIrfxaIuv8axA63J8eJoiqjBIlRalCZwETOxp5aEA7kCjZkATO5GZMXdmz5rQOpF05j5+mFNeo2ZeeDeUu+/zP1Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Value = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),

distribution = Table.AddColumn(#"Changed Type", "distribution", each Text.Format("#[left]-#[right]",[left=Number.IntegerDivide([Value]/10,1)*10,right=left+9])),

sort = Table.Sort(distribution,{"distribution", 0})

in

sort

Result:

Solution 2:

For example, if there is no product in the 10-20 value group, the row will not be displayed according to the method of grouping in solution1. We can use an alternative method:

First, build up each value group.
Filter out those value within each group range.
Count the rows in the filtered table.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc7LDYAwDAPQXXLuAVqKuPIrfxaIuv8axA63J8eJoiqjBIlRalCZwETOxp5aEA7kCjZkATO5GZMXdmz5rQOpF05j5+mFNeo2ZeeDeUu+/zP1Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Value = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),

distribution = Table.FromColumns({List.Transform(List.Numbers(0,7,10),each Text.Format("#{0}-#{1}",{_,_+9}))},{"distribution"}),

group = Table.AddColumn(distribution, "count", each Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Value]>=Number.From(Text.Split([distribution],"-"){0}) and x[Value]<Number.From(Text.Split([distribution],"-"){1}))))

in

group

Result:

Solution 3:

Power BI Desktop also has built-in feature to create new group, but the form of axis is fixed. If you want to customize the form of axis, you can refer to the first and second solutions.

Right click the Axis field and select new group.