Splitting Text Column - Tables

[Anonymous]

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!

[v-zhangti · Accepted solution]

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.

[PaulOlding]

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.

[Whitewater100]

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])