Forum Discussion
Solved
Count values with delimiter
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.
5 Replies
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"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?
Hi,
In Power Query, you can split the column by rows using , as a delimiter.
