Forum Discussion
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 | 44 |
| C | 77 |
| D | 55 |
| D | 44 |
| D | 77 |
New
| Field-MainCategory | Field-subcategory | Field-scount |
| A | 55 | 2 |
| A | 33 | 2 |
| B | 33 | 2 |
| B | 44 | 2 |
| C | 77 | 1 |
| D | 55 | 3 |
| D | 44 | 3 |
| D | 77 | 3 |
Basically - I am looking to create a new field Field-scount which is a count of occurances of the Field-MainCategory,
There are other important columns in the table so a group by will not do the trick.
Any ideas ? Thanks - Jerry
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
1 Reply
- AlienSx
Super User
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