Splitting Cell Values and then distinct counting them

[Anonymous]

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)

[AlexisOlson · Accepted 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"

[Anonymous]

Thank you so much! This made everything so much simplier~

[mahoneypat]

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

[Anonymous]

Hi there, thanks for this solution! 🙂