Forum Discussion
Count Values between Tables with delimeter
Hello, I have two tables. One Table is a list of Codes that I need to get a count of appearences from a Column in a connected table (One to Many Single Way)
In the connected table the Column has these codes listed however can have multiple different listed in each row seperated by a Semicolon Delimiter.
For Example for the two tables and what it would result in.
| Code |
| AB; ABC; ABCD |
| ABC; ABCD |
| AB |
| ABCD |
| Code | Count |
| AB | 2 |
| ABC | 2 |
| ABCD | 3 |
Anonymous ,
Easiest solution I have is as follows:
In PQ, duplicate your original table.
Then Remove all Columns except the [Code].
Then you can use my original solution.
The drawback here is you would lose any relationships to the other data in the original table, but if you can live with that, then this should work for you.
If you cannot live with this alternative, I suggest you close this thread, and repost your original question (I will continue to ponder). But this will give others here the chance to perhaps provide a more elegant solution that would better suit your needs.
Kind Regards,
10 Replies
It seems that there's an interaction ID in the data that you could bring into the duplicated table then Split the code column by the pipe, unpivot on everything but the interaction ID and join back to the original table on the interaction ID.
- rsbin
Community Champion
Anonymous ,
Use Power Query to transform your first table. You want to split by Delimiter into Rows.
Then create a new Summary Table to do your count.
Please see attached pbix for details.
Hope this gets you on your way.
Regards,
- rsbin
Anonymous ,
Can you please post a sample table with a couple of these additional columns as text. Please use fictitious data if necessary.
