Count how many times the ID in sheet A shows up in sheet B

[Grewin]

I wanted to count how many times the ID in sheet A shows up in sheet B.

Sheet A:

Page Tag	Id
A	1259
B	1221
C	1223
D	763
E	1247
F	1237
G	1235

Sheet B:

Tag Ids
923, 815, 763, 1235
851, 1259
903, 899, 927, 935
915, 921, 815, 1223
1235
564, 737, 1259, 1223
943, 1197, 1247, 1125, 1123, 1195, 1235, 1237

Desired Output:

Page Tag	Count
A	2
B	0
C	2
D	1
E	1
F	1
G	3

Thank you in advance.

[v-cgao-msft · Accepted solution]

Hi @Grewin ,

My steps are:

1. In Power Query Eidtor -> Split column by delimiter -> select all column -> unpivot column

2. In Sheet A -> Merge queries -> aggregate

3. result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[amitchandak]

@Grewin , Split the column tag id in table two into rows

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

And group the table with count (if needed duplicate)

https://docs.microsoft.com/en-us/power-query/group-by

Then merge the two tables.

 : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

or Also step 1

come to DAX and create a new column in Table 1

COuntx(filter(Table2, Table1[Tag id] = Table2[Tag_ids]) , Table2[Tag id])