Count comma delimited data

[WakeTurbulence]

Hello,

I have a data dump whereby some fields can have more than one type in the same occurrence and other fields are multi-select which can give multiple categories in the same occurrence. I want to be able to count the total for each cell in Power BI but the solution seems to elude me despite searching exhaustively on the net. I'm sure the answer must be simple but can't seem to wrap my head around what I need to do.

Example:

Goal (aside from being able to slice, for example, "all occurrence categories for type a"):

I also recognize that I can split the columns based on the comma delimiter but again, how would i agregate the data so that i am able to slice on a per occurrence basis? splitting by delimiter would work fine for the types but some of the categories have commas in their name so we would end up with categories being split in half and then not matching with the reference data.

[Ashish_Mathur · Accepted solution]

Hi,

I do not have a fancy way to solve this.  All i can suggest is that we create two tables fro your source as shown below.  I have done so by using the Split column > By rows feature of Power Query.

[Guido_Beulen]

Add a column in Power Query M with: 

= Table.AddColumn(#"[laststep]", "Count separated values", each Text.Length([ColumWhereToCountSeparatedValues])-Text.Length(Replacer.ReplaceText([ColumWhereToCountSeparatedValues],";",""))+1)

In this example the seperator is ";"

The DAX variation in form of a measure is: 

CountOfItems = LEN(MAX(List[List])) - LEN(SUBSTITUTE(MAX(List[List]),",","")) + 1

 Which was discussed in this topic.

[Ashish_Mathur · Accepted solution]

Hi,

I do not have a fancy way to solve this.  All i can suggest is that we create two tables fro your source as shown below.  I have done so by using the Split column > By rows feature of Power Query.

[WakeTurbulence]

Thanks Ashish,

I was hoping to avoid this though. Does anyone else have any thoughts?

[WakeTurbulence]

I have used power query to split the column into several columns so now i have columns: `type.1` `type.2``type.3` etc. in table 1. These columns all relate to the column `Type` in table 2. However, when i create relationships from each of the columns in table 1 to column 2 only the first one I create is active and the remainder are inactive. As a result, power BI is only counting the active relationship in its output. How can i make it count all of them as one statistic?

In addition to the answer above, I would still prefer to leave this column unsplit and somehow count each report entered for one occurrence. for the most part there will only be 3 - 4 report types per occurrence but on the rare occasion there could be upwards of 20 reports for one occurrence. Obviously having 20 columns just for the off chance that this occurs is not ideal but if I limit the amount of columns the origin column is split in to say 10 then I am possibly loosing out on data.

[Ashish_Mathur]

Hi,

Please show your actual data and your expected result.

[WakeTurbulence]

occurrence #	report type	date	route	location
1	I	21/08/2017 0:00	1	A
2	I	22/08/2017 0:00	4	B
3	I	22/08/2017 0:00	6	G
4	I	23/08/2017 2:05	3	T
5	I	23/08/2017 0:00	6	V
6	G	23/08/2017 15:35	7	T
7	C,F,F	23/08/2017 13:30	9	T
8	V,F	22/08/2017 16:01	4	T
9	I	24/08/2017 0:00	2	E
10	C	24/08/2017 18:00	4	E
11	I	22/08/2017 18:10	6	G
12	F,F,C,G	23/08/2017 19:00	8	B
13	I	23/08/2017 12:00	5	A
14	I	24/08/2017 17:00	2	A
15	G	22/08/2017 4:56	2	T
16	G	07/08/2017 15:45	1	V
17	G	18/08/2017 11:25	3	V
18	G	18/08/2017 11:30	6	L
19	I	20/08/2017 23:00	8	M
20	G	21/08/2017 7:00	9	M

I cant show the actual data but here is the same idea. I want to be able to have, say, a pie chart which shows the distribution of report type. When I slice the data for location T for example, I want to be able to see what report types are related to location T. I can get this result fine but the problem lies in the rows were there are multiple report types in one occurrence; Power BI does not count those results, it only counts the rows where there is only 1 type. If I split the report type column into multiple columns (type.1, type.2, type.3, etc), the new problem is that Power BI only counts the first column because the other relationships are rendered inactive because they all relate to the same column in table 2.

[Ashish_Mathur · Accepted solution]

Hi,

Do not split the data by columns.  SPlit it by rows as suggested in my previous post.

[WakeTurbulence]

but then the data will no longer be 'per occurrence'

[Ashish_Mathur]

Hi,

I believe it will if you use the the DISTINCTCOUNT() function.

[WakeTurbulence]

I should also say that another goal is to simply count the total number of each report type submitted without slicing. 

[cibgks]

Did you find any solution? I have been trying to solve this exact problem for 1 week already

[WakeTurbulence]

Hi,

The solution from Ashish is pretty much it. Splitting the delimited data into rows works best. The id number will get duplicated but BI will accurately count each instance of the category.

For me this causes an unwanted duplication of all my other fields so my solutution for this is to split the category information out into a separate table that is linked to the host data table via the ID column.

I'm sure theres a better, more elegant way, but its working for me at this time until I sit down and try to work out a more elegant solution.

Wake

[WakeTurbulence]

I should add that I do this for each field which contains delimited data.

I then have a measure which uses ROUNTROWS to get the total.

TOTAL = COUNTROWS(tblCategory)

[Greg_Deckler]

Just to clarify, from your sample data, the Types image should show an "a" of 3, correct? Because a occurs 3 times in your sample data?

[WakeTurbulence]

yes that is correct. I just put together the example in excel quickly and mis-counted.

[WakeTurbulence]

Bump.