Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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!
Solved! Go to 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"
@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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
75 | |
63 | |
52 | |
47 |
User | Count |
---|---|
218 | |
86 | |
64 | |
63 | |
60 |