Grouping function in Power BI

peter8848

Board Regular
Joined
Oct 7, 2018
Messages
112
Hi All,

When we have the data ready for Power BI, does it have any in house function we can group the one column of the data? For instance, I have one data set and one column is the age from 17 to 99 so Does Power BI can group them somehow or customized so we have three age groups say,17-33,34-60,61-99. A bit like excel we can group the date into month, quarter, year etc?

So visualization will only show three level of the data instead of wide range from 17 to 99?

Thanks in advance.

Peter
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
like this or I misunderstood?

NameAgeNameAge
A
29​
C17 - 19
B
18​
B17 - 19
C
17​
F17 - 19
D
20​
D17 - 19
E
18​
E17 - 19
F
22​
I20 - 23
A
23​
F20 - 23
B
29​
A20 - 23
I
20​
D20 - 23
C
21​
C20 - 23
D
17​
B27 - 30
E
27​
A27 - 30
F
19​
N27 - 30
N
30​
E27 - 30
 
Upvote 0
For instance:

Code:
let    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Changed_type = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}}),
    Userdef_Col = Table.AddColumn(Changed_type, "Age_group", 
        each      if [Age] < 17 then "younger than 1st group" 
             else if [Age] < 34 then "Age group 17-33" 
             else if [Age] < 61 then "Age group 34-60"
             else if [Age] > 99 then "older than 3rd group" 
             else                    "Age Group 61-99")
in
    Userdef_Col
 
Upvote 0
Hi pinarello
user-offline.png
,

Thanks for your code, just wondering does Power BI have this in build function instead of creating codes?

Cheers,

Peter
 
Upvote 0
yes this is exactly what we need, does BI support this internally?

this is M-code (PowerQuery) so PBI support it by default
and no, there is no buil-in function for this

adapt to your needs

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "Goups", each if [Age] >= 17 and [Age] <= 19 then 1 else if [Age] >= 20 and [Age] <= 23 then 2 else if [Age] >= 24 and [Age] <= 30 then 3 else null),
    Group = Table.Group(IF, {"Goups"}, {{"Count", each _, type table}, {"Min", each List.Min([Age]), type number}, {"Max", each List.Max([Age]), type number}}),
    Expand = Table.ExpandTableColumn(Group, "Count", {"Name"}, {"Name"}),
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Expand, {{"Min", type text}, {"Max", type text}}, "en-GB"),{"Min", "Max"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Age"),
    ROC = Table.SelectColumns(Merge,{"Name", "Age"}),
    Sort = Table.Sort(ROC,{{"Age", Order.Ascending}})
in
    Sort[/SIZE]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top