Count values with delimiter

[kingy614]

I have a data set like below:

Column 1	Column 2
PLF	ARM
PLF	LEG
PLF	ARM, LEG
STATIC	ARM

I created a heirarchy with both columns in which Column 1 is first. I want to have Column 2 be split by the comma delimiter so when you drill down in the chart for PLF is shows the count of each value accurately. I can get Colunm 2 to display ARM, LEG, and ARM, LEG with each being a count of 1. I want to show ARM and LEG seperatly with the count of ARM being 2 and LEG being 2 for PLF.

[Ashish_Mathur]

Hi,

In Power Query, you can split the column by rows using , as a delimiter.

[ryan_mayu · Accepted solution]

@kingy614 

you can try to split delimiter to rows in PQ

[Greg_Deckler]

@kingy614 In Power Query, split by delimiter and then unpivot like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvBxU9JRcgzyVYrVgfF8XN2ReEA5HQWYUHCIY4inM0xHLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column 2", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column 2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column 2", type text}})
in
    #"Changed Type1"

[kingy614]

I copied and pasted the code and that did work but how would I use this with a SharePoint Online list? Is there anything I need to change in the code to use the SharePoint Online list?

[sevenhills]

https://learn.microsoft.com/en-us/power-query/connectors/sharepoint-list

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-sharepoint-online-list

https://cmdrkeene.com/a-faster-way-to-get-data-from-sharepoint-lists-in-power-query/

Hope this links helps!