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 |
JBusque ,
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.
Sorry I should of been more descriptive. There is other columns in the First Table that Can't really be split due to needing specific counts based on those. Is there a way to do this outside of splitting them?
If this is able to be done in a measure without use of the 2nd table that would be a viable solution for my needs as well
Hiļ¼JBusque
Can you tell me if your problem is solved? If yes, please accept it as solution.
Best Regards,
Leroy Lu
