Pivot Table Comma Delimited Values

[ninjasushi]

Hi All,

I have a table with tags separated with commas for each row like below:

Date	Item No	Tag
1/3/2022	1​	Tag1, Tag2, Tag3
1/5/2022	2​	Tag2, Tag4
1/8/2022	3​	Tag5
1/3/2022	4​	Tag1, Tag2, Tag5
1/6/2022	5​	Tag7, Tag11
1/3/2022	6​	Tag1, Tag11
1/5/2022	7​	Tag2, Tag8
1/4/2022	8​	Tag1, Tag2, Tag6, Tag9
1/10/2022	9​	Tag3, Tag4
1/8/2022	10​	Tag3, Tag7, Tag12​

I'm trying to create a pivot table that will filter the items based on the date and will count the tags for the filtered items. In example; On 1/3/2022 only item nos. 1, 4 and 6 were active and these three item nos had a total of three tag1, two tag2, one tag3, one tag5 and one tag11. By this way i can see which tag was the most used for the given specific date. Once have this as a pivot table then want to create a chart to express the same. My goal here is to find the most used tag within the given date.

Been trying to find a way to do this and couldn't manage yet. Hope someone can help me out. Thank you for your time in advance!

Cheers!

 

[severynm]

Hi!

So I think to get to your end goal, we'll need to do a little data transformation. What I would do is load that table into Power Query, split up the tag column by delimiter, and then unpivot the split tag columns back into one. The query output is a three column table that you can then throw into a pivot chart or table and aggregate how you want.

 

[ninjasushi]

Hi!

So I think to get to your end goal, we'll need to do a little data transformation. What I would do is load that table into Power Query, split up the tag column by delimiter, and then unpivot the split tag columns back into one. The query output is a three column table that you can then throw into a pivot chart or table and aggregate how you want.
View attachment 60411

Hi there!

Thanks for your kind reply and time here. It's a learning process for me and wondering if the Power Query and the pivot table will update automatically when I add more dates to my main table?

Thanks again!

 

[severynm]

If you mean automatic as in requiring no user intervention, then no. You will have to trigger the refresh somehow.

The PQ output table and the pivot table are technically different items so you could refresh them separately from their respective tabs in the ribbon, but you can also refresh everything at once with the Refresh All command on the Data ribbon. It's also possible to trigger the refreshes with VBA, but I'd recommend against that unless there is a need to.