Grouping function in Power BI

[peter8848]

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

 

[sandy666]

like this or I misunderstood?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Age[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Age[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]

29​

[/td][td][/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]17 - 19[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]

18​

[/td][td][/td][td]B[/td][td]17 - 19[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]C[/td][td=bgcolor:#DDEBF7]

17​

[/td][td][/td][td=bgcolor:#E2EFDA]F[/td][td=bgcolor:#E2EFDA]17 - 19[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D[/td][td]

20​

[/td][td][/td][td]D[/td][td]17 - 19[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]E[/td][td=bgcolor:#DDEBF7]

18​

[/td][td][/td][td=bgcolor:#E2EFDA]E[/td][td=bgcolor:#E2EFDA]17 - 19[/td][/tr]

[tr=bgcolor:#FFFFFF][td]F[/td][td]

22​

[/td][td][/td][td]I[/td][td]20 - 23[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]

23​

[/td][td][/td][td=bgcolor:#E2EFDA]F[/td][td=bgcolor:#E2EFDA]20 - 23[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]

29​

[/td][td][/td][td]A[/td][td]20 - 23[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]I[/td][td=bgcolor:#DDEBF7]

20​

[/td][td][/td][td=bgcolor:#E2EFDA]D[/td][td=bgcolor:#E2EFDA]20 - 23[/td][/tr]

[tr=bgcolor:#FFFFFF][td]C[/td][td]

21​

[/td][td][/td][td]C[/td][td]20 - 23[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]D[/td][td=bgcolor:#DDEBF7]

17​

[/td][td][/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]27 - 30[/td][/tr]

[tr=bgcolor:#FFFFFF][td]E[/td][td]

27​

[/td][td][/td][td]A[/td][td]27 - 30[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]F[/td][td=bgcolor:#DDEBF7]

19​

[/td][td][/td][td=bgcolor:#E2EFDA]N[/td][td=bgcolor:#E2EFDA]27 - 30[/td][/tr]

[tr=bgcolor:#FFFFFF][td]N[/td][td]

30​

[/td][td][/td][td]E[/td][td]27 - 30[/td][/tr]
[/table]

 

[pinarello]

For instance:

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

 

[peter8848]

Hi sandy666 ,

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

Cheers,

Peter

 

[peter8848]

Hi pinarello

,

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

Cheers,

Peter

 

[sandy666]

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

[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]