Count of an Occurrence in multiple columns

[Kaskazi_Network]

Hi all,

So I have this dataset as shown in dummy table below.  I need to get a count of each sku and show that it's available in a certain number of outlets. I have split the 'availble_sku' column using the comma delimiter but now I need a DAX/PowerQuery formula to get that count.

Outlet id	available_sku
33	sku1,sku3
44	sku2
77	sku2,sku3,sku1,sku4,sku5
56	sku3,sku4
23	sku5
12	sku6,sku5

My Desired output is as below:

available_skus	No of Outlets
sku1	2
sku2	2
sku3	3
sku4	2
sku5	3
sku6	1

Any assistance to solving this, will be highly appreciated. Thank you.

[HotChilli · Accepted solution]

After the 'split by delimiter' in Power Query, select the first column and 'Unpivot other columns'.

Close and Apply

Create a table visual with the SKU's in the first column, put COUNT of outlets in the 2nd column

[Vera_33 · Accepted solution]

Hi @Kaskazi_Network 

If you want to use Power Query, put below M code in Advanced Editor with a blank query. If you want to use DAX, you can stay with step #"Expanded available_sku", then simply use COUNTROWS

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZW0lEqzi411AESxkqxOtFKJiYQISMwz9wcygMr0IEpNQERpmAVpmYQFWBJE7CQEdRUiAJDIwjPDKonFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Outlet id" = _t, available_sku = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Outlet id", Int64.Type}, {"available_sku", type text}}),
Custom1 = Table.TransformColumns( #"Changed Type",{"available_sku", each Text.Split(_,",")}),
#"Expanded available_sku" = Table.ExpandListColumn(Custom1, "available_sku"),
#"Grouped Rows" = Table.Group(#"Expanded available_sku", {"available_sku"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"

[HotChilli · Accepted solution]

After the 'split by delimiter' in Power Query, select the first column and 'Unpivot other columns'.

Close and Apply

Create a table visual with the SKU's in the first column, put COUNT of outlets in the 2nd column