Get Counts of Items across columns

[RolandPlanet]

I have data structured like this:

ID	Item1	Item2	Item3
1	apple	orange	pear
2	apple	grape	pear
3	orange	banana	pear

What I'm trying to do is display data in a visual that's a count of the items across all three columns. In other words, I'd like it to display the data like: 

pear - 3

apple - 2

orange - 2

grape - 1

banana - 1

[v-yuta-msft · Accepted solution]

@RolandPlanet ,

In query editor, click on column [Item1], [Item2] and [Item3] then click Transform-> Unpivot Columns. Then in transform pane, click group by like below:

 

The result is as below:

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @RolandPlanet ,

You just need to unpivot the item columns.

Check this file as an example: Download PBIX 

Ricardo