Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
I am trying to split a column with multiple cell values and then hoping to create a visual to count how many times these values appear. I've found the answer to splitting the cells but am having trouble trying to create the visual. I am currently stuck at step 2 as I'm not too sure how we can combine the different columns that were broken down and put them into a visual. Any help would be great 🙂
Step 1: Splitting values
Step 2: Distinct Count how many times each value appear (What I want to achieve on my Canvas)
Solved! Go to Solution.
I'd recommend changing step one to split into rows instead of columns.
If you do this, then you can finish with two clicks: Group By > OK.
Here's a full sample query you can paste into the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyEnVUQhITSzSUXBKzANCpVidaCXvzPJMHQXf1Jz8PIgsWNQptaioEsICK9VRgOr3L0rMS08FywRk5qUmIovCFUHMhBgWCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t]),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Category", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Category"),
#"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Category"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
I'd recommend changing step one to split into rows instead of columns.
If you do this, then you can finish with two clicks: Group By > OK.
Here's a full sample query you can paste into the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyEnVUQhITSzSUXBKzANCpVidaCXvzPJMHQXf1Jz8PIgsWNQptaioEsICK9VRgOr3L0rMS08FywRk5qUmIovCFUHMhBgWCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t]),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Category", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Category"),
#"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Category"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
Thank you so much! This made everything so much simplier~
You should unpivot the columns those columns to make your analysis easier. Select the first column in the query editor, right click and choose "Unpivot Other Columns". Delete the first column. Then you should have two columns - "Attribute" (Fruit1, Fruit2, Fruit3, ...) and "Value" (Apple, Pear, Melon, Orange, ...). You can then put the Value column in a table (Don't Aggregate) and a simple measure of COUNTROWS(YourTableName).
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi there, thanks for this solution! 🙂