Forum Discussion
Splitting Cell Values and then distinct counting them
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)
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"
4 Replies
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"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
