Forum Discussion

jerryr0125's avatar
jerryr0125
New Member
2 years ago
Solved

Count of subcategories

Hi everyone  Using Power Query - I am looking to do a count of subcategories by creating a new custom column. Example:   Field-MainCategory Field-subcategory A 55 A 33 B 33 B ...
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    2 years ago

    group by category, keep all rows as one column, count rows in 2nd and expand table column after. 

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
    group = Table.Group(Source, "Field-MainCategory", {{"all", each _}, {"Field-scount", Table.RowCount}}),
    expand = Table.ExpandTableColumn(group, "all", {"Field-subcategory"})
in
    expand
AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
2 years ago

group by category, keep all rows as one column, count rows in 2nd and expand table column after. 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
    group = Table.Group(Source, "Field-MainCategory", {{"all", each _}, {"Field-scount", Table.RowCount}}),
    expand = Table.ExpandTableColumn(group, "all", {"Field-subcategory"})
in
    expand