Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JBusque
Regular Visitor

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

 

CodeCount
AB2
ABC2
ABCD3
1 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,

View solution in original post

10 REPLIES 10
v-linyulu-msft
Community Support
Community Support

Hi,@JBusque 

 

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

 

Best Regards,

Leroy Lu

newellaa
Frequent Visitor

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
Regular Visitor

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
Super User
Super User

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

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?

@JBusque ,

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

DateDurationCodeNames InteractedInteraction IDTotal HoldTotal TalkTotal Handle
4/2754764ABFrank64334343346246236211515
4/286535AB; ABCFrank; George8116546696134723432352
5/1215282ABCDAllison643496619162362362362236
5/1324362ABC; ABCDTony

89416161647

   
5/20563ABCCassandra64111919116 73473437


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

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

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.