count values from multiple columns

[JMMSSAU]

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

[JMMSSAU]

That's brilliant thanks.

[Natarajan_M · Accepted solution]

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 rows

Once 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 notebook

For more Power BI tips and discussions, let's connect on LinkedIn.

Cheers!