Forum Discussion
Splitting Text Column - Tables
Hi,
Unsure if this is the correct placing for this question. I essentially have data similar to the below:
Date|Quote|Product
01.01.22|001|A, B
01.01.22|002|A, B, C
01.01.22|003|B
01.01.22|004|A, C
01.01.22|005|A
01.01.22|006|B, A
From the above, I want to create a table that would count how many times A, B and C would appear. So the expected result would be as below:
Product|Count
A|5
B|4
C|3
I can split the data lines for example but that will mean I will have to create three different tables as B for example will appear in column 1 and column 2. Thanks in advance!
Hi, Anonymous
You can try the following methods.
Step 1:
Step 2:
Step 3:
Step 3:
Measure:
Count = CALCULATE(COUNT('Table'[Product]),FILTER(ALL('Table'),[Product]=SELECTEDVALUE('Table'[Product])))Is this the result you were expecting?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi:
Besides in Power Query, here is one DAX solution.
You can make three calc columns in your table. One for each letter. Here's an example, I named your table "Table":
A's = LEN(Data[Product])- LEN(SUBSTITUTE( SUBSTITUTE(Data[Product], "a", ""), "A", ""))Then do 3 measures to sum them: Here's the first one:No A's = SUM(Data[A's])Hi Anonymous
I would use Power Query to split and unpivot this data so you get a separate row for each product.
It can be done in one 'Split Column By Delimiter' step. You'll need to expand the Advanced Options and choose rows.
that will give you a table that looks like this
and finally you can use a measure that does COUNTROWS(Table) to give you how many times the products appear.
Hi, Anonymous
You can try the following methods.
Step 1:
Step 2:
Step 3:
Step 3:
Measure:
Count = CALCULATE(COUNT('Table'[Product]),FILTER(ALL('Table'),[Product]=SELECTEDVALUE('Table'[Product])))Is this the result you were expecting?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
