Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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
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
@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])
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |