Forum Discussion
Solved
Splitting Multiple Columns by Delimiter and Counting Multiple Values
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!
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"
1 Reply
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"