Forum Discussion
Count comma delimited data
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.
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.
Hi,
Do not split the data by columns. SPlit it by rows as suggested in my previous post.
17 Replies
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.
Thanks Ashish,
I was hoping to avoid this though. Does anyone else have any thoughts?
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.
When I tried this solution it made me switch to import mode, instead I create a DAX measure and lookup tables to seperate the comma seperated values.
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?
yes that is correct. I just put together the example in excel quickly and mis-counted.
Bump.
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.
