Forum Discussion
awsiya
Helper I
2 years agoClustured Column chart (multiple values in column)
I need a little help in powerBi Desktop. I have a simple table of 1 column "Three things you liked with us". and customers choose 3 choices given in a survey (they can choose less than 3 also). In th...
- 2 years ago
You need to do a little data scrubbing
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fYw7CoAwEAWvsqTeU6h4ANuQQuNTFsImmDTeXhS/je0wM9aaRmaVsjJ1yAm+MLUxjsaxNT6g1yCKnJmmi9b9AqYzO8i92B2mXXiZFYJg+orDw352CzxSkahM/ni6DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Three things you liked with us" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Three things you liked with us", type text}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Three things you liked with us", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Three things you liked with us"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Three things you liked with us", type text}}), #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Three things you liked with us", Text.Trim, type text}}), #"Capitalized Each Word" = Table.TransformColumns(#"Trimmed Text",{{"Three things you liked with us", Text.Proper, type text}}) in #"Capitalized Each Word"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
And after that the visual will be very easy.
lbendlin
Super User
2 years agoYou need to do a little data scrubbing
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fYw7CoAwEAWvsqTeU6h4ANuQQuNTFsImmDTeXhS/je0wM9aaRmaVsjJ1yAm+MLUxjsaxNT6g1yCKnJmmi9b9AqYzO8i92B2mXXiZFYJg+orDw352CzxSkahM/ni6DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Three things you liked with us" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Three things you liked with us", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Three things you liked with us", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Three things you liked with us"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Three things you liked with us", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Three things you liked with us", Text.Trim, type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Trimmed Text",{{"Three things you liked with us", Text.Proper, type text}})
in
#"Capitalized Each Word"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
And after that the visual will be very easy.