Forum Discussion
count values from multiple columns
HI All,
Issue:
I have 6 columns with items listed, some duplicates over different rows. I am needing to count the items across the 6 columns, display them in a single table, then count them
Example of Data:
ID | Item 1 | Item 2 | Item 3 | Item 4 | Item 5 | Item 6 |
01 | Household Essentials | Vehicle & Transport | ||||
| 01 | Furniture | Repair & Maintenance | ||||
| 01 | Baby Furniture & Items | Repair & Maintenance | ||||
| 02 | Education & Employment | |||||
| 02 | Technology | |||||
| 02 | Tablet / Laptop | |||||
| 03 | Vehicle & Transport | Vehicle & Transport | ||||
| 03 | Vehicle Registration | Vehicle Registration | ||||
| 03 | Car Registration | CTP |
Desired outcome:
| Items | Count |
| Household Essentials | 1 |
| Car Registration | 1 |
| Vehicle & Transport | 3 |
| Tablet / Laptop | 1 |
| Technology | 1 |
| Education & Employment | 1 |
| Baby Furniture & Items | 1 |
| Furniture | 1 |
| Repair & Maintenance | 2 |
| CTP | 1 |
Background:
I have a large spreadsheet that has items in 4 columns that were separated by a dash. This column was split and rows duplicated so that there is multiple rows with the same ID, however now one Item 1 on each row. the same for Item 2.
Any help would be amazing thanks
Hi JMMSSAU , Before counting, I'd suggest first unpivoting the 6 item columns into a single column ,this makes the count much simpler and cleaner to work with.
In Power Query:
1. Select all 6 item columns (Item 1 through Item 6)
2. Right-click → Unpivot Columns
3. This gives you a single Value column with all items stacked
4. Remove the Attribute column (which just says Item 1, Item 2 etc.)
5. Filter out any blank/null rows
6. Then group by the Value column and count rowsOnce everything is in one column, a simple **Group By** gives you exactly the Items + Count table you're after — no complex cross-column logic needed.
Hope that helps!
Thanks!
Natarajan Manivasagan
If you found this helpful, please consider giving it a Kudos and marking it as the accepted solution — it goes a long way in helping others facing the same issue.🏆 Best Solution for Enterprise BI — 2026 Microsoft Fabric Semantic Link Developer Experience Challenge
👉 Microsoft announcement · View the winning notebookFor more Power BI tips and discussions, let's connect on LinkedIn.
Cheers!
2 Replies
Hi JMMSSAU , Before counting, I'd suggest first unpivoting the 6 item columns into a single column ,this makes the count much simpler and cleaner to work with.
In Power Query:
1. Select all 6 item columns (Item 1 through Item 6)
2. Right-click → Unpivot Columns
3. This gives you a single Value column with all items stacked
4. Remove the Attribute column (which just says Item 1, Item 2 etc.)
5. Filter out any blank/null rows
6. Then group by the Value column and count rowsOnce everything is in one column, a simple **Group By** gives you exactly the Items + Count table you're after — no complex cross-column logic needed.
Hope that helps!
Thanks!
Natarajan Manivasagan
If you found this helpful, please consider giving it a Kudos and marking it as the accepted solution — it goes a long way in helping others facing the same issue.🏆 Best Solution for Enterprise BI — 2026 Microsoft Fabric Semantic Link Developer Experience Challenge
👉 Microsoft announcement · View the winning notebookFor more Power BI tips and discussions, let's connect on LinkedIn.
Cheers!
That's brilliant thanks.
