Count of multiple selections

[Anonymous]

I've a sharepoint that I use to report results. One of the columns in the sharepoint has multi selection.

How can I find a way to create visualization when there are multi-selections?

e.g.

ID	Name	Preferred Colors
1	John	Red, Blue, White
2	Marie	White, Blue, Red
3	Steve	White, Red
4	Allison	Pink, Red

I want to be able to have a table that looks like this:

Color	Count
Red	4
Blue	2
Pink	1
White	3

The SharePoint column shows in PowerBI as [List]

When I try to expand it, it looks like this

["Red", "Blue", "White"]

["White", "Blue", "Red"]

["White", "Red"]

Thanks in Advance

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download sample PBIX file

You can extract the Lists to separate rows in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8hTitWJVjICcnwTizJTwTxjIC+4JLUMwjMB8hxzcjKL84FqYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Preferred Colors", each if [Name] = "John" then {"Red", "Blue", "White"} 

else if [Name] = "Marie" then {"White", "Blue", "Red"}

else if [Name] = "Steve" then {"White", "Red"} 

else {"Pink", "Red"}),
    #"Expanded Preferred Colors" = Table.ExpandListColumn(#"Added Custom", "Preferred Colors")
in
    #"Expanded Preferred Colors"

Close PQ then add a table showing Preferred Colors in the 1st column and Count of Preferred Colors in the 2nd column

Regards

Phil