Count Values between Tables with delimeter

[JBusque]

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]

Hi，@JBusque 

Can you tell me if your problem is solved? If yes, please accept it as solution.

Best Regards,

Leroy Lu

[newellaa]

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. 

[JBusque]

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

[rsbin]

@JBusque ,

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,

[JBusque]

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?

[rsbin]

@JBusque ,

Can you please post a sample table with a couple of these additional columns as text.  Please use fictitious data if necessary.

[JBusque]

Date	Duration	Code	Names Interacted	Interaction ID	Total Hold	Total Talk	Total Handle
4/27	54764	AB	Frank	64334343346	246	23621	1515
4/28	6535	AB; ABC	Frank; George	81165466961	347	234	32352
5/12	15282	ABCD	Allison	64349661916	236236	2362	236
5/13	24362	ABC; ABCD	Tony	89416161647
5/20	563	ABC	Cassandra	64111919116		7347	3437

Note - Numbers are completely ficticious and don't actually really equal anything.

[rsbin]

@JBusque ,

How may distinct Codes do you have?

And, what is the max number of Codes you may have in a single cell?
These will help me determine if my next solution is feasible.

Thanks,

[JBusque]

34 Distinct currently, Max is variable but in current dataset I have its up to 5 in a single cell. Could potentially be more.

[rsbin · Accepted solution]

@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,