Splitting Multiple Columns by Delimiter and Counting Multiple Values

[chris886]

I've been searching but my issues seems to be different than a lot of the 'splitting multiple columns' solutions I've seen. My values are just multiple options within a form and do not have any relationship to the other columns, but I need to be able to measure them individually.

Here's my sample data:

Index	Is Public	Color	Shape	Size
1	Yes	Red, Yellow, Green	Square, Circle	Small, "Large, Tall"
2	Yes	Yellow, Blue	Circle	Small
3	No	Red, Green, Black	Square, Triangle	Medium, "Large, Tall"
4	Yes	White	Triangle, Rectangle	Small, Medium
5	No	Green, Black	Rectangle	Medium

The index column serves as each form submission, and is intended to be my 'event count'. Beyond that, I just want to be able to measure/visualize each column of data separately. A table showing counts for each color, another showing counts for each shape, etc.

I've tried splitting column into rows and columns, but neither seems to work. Rows will break out the data into false events and splitting into columns doesn't allow me to count all the values in one group. I'm also can't figure out how to delimit the 'Large, Tall' item since it includes a comma within the text value.

Appreciate any help!

[Greg_Deckler · Accepted solution]

@chris886 Might be better ways but see if this works. PBIX is attached below signature.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY69DsIwDIRfJfLshb8XgIEFGEolVKUdotYqEW4rQiNenyTgqiAWS7bvuzutYQEIBT3CzKhBVRDz8ES1d0R9OJ7v3jhCtbOuZoqHzjCjKuFgXBseeVhLgAo1LCcrcdmyj8w3nLSrsJ0GSU1pUW7q2yw0d9b0bSKP1Fjf/c9dT7mXqx2jWkBUGdWjeHyqv60SuZEWPwXmlMirFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, #"Is Public" = _t, Color = _t, Shape = _t, Size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Is Public", type text}, {"Color", type text}, {"Shape", type text}, {"Size", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Size", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Size.1", "Size.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Size.1", type text}, {"Size.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Shape", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Shape.1", "Shape.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Shape.1", type text}, {"Shape.2", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Color", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Color.1", "Color.2", "Color.3"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Color.1", type text}, {"Color.2", type text}, {"Color.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type3", {"Index", "Is Public"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text",".1","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".2","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".3","",Replacer.ReplaceText,{"Attribute"})
in
    #"Replaced Value2"