Struggling to Create Stacked Bar Chart Showing Location Preferences

[EMC2]

Hi everyone, hoping for some help creating a stacked bar chart with the data I have. If a stacked bar chart is not the best option, I'm definitely open to any other ideas for how to present this.

The goal is to show how many members total ranked each location, broken down by many ranked it 1st, 2nd, 3rd, etc. I hope this makes sense. 

I have 10 possible location choices, and each individual ranks their choices. My assumption was to create a horizontal bar chart where the locations are listed on the Y axis and the legend color codes each preference category (pref 1, pref 2, pref 3, etc.). So the goal is to have a bar for Oklahoma and within that bar, how many ranked it 1st, 2nd, 3rd, etc. I hope this makes sense. 

An example of how the data is formatted is below. The member numbers are unique and are formatted as text.

Member Number	Preference 1	Preference 2	Preference 3
1234	Oklahoma	New York	Florida
5678	New York	Florida	South Carolina
9101	Florida	Oklahoma	South Carolina
1112	South Carolina	Florida	n/a

[lbendlin · Accepted solution]

First step - unpivot your data to make it usable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUfLPzknMyM9NBDL9UssVIvOLsoFMt5z8osyURKVYnWglUzNzC+yyOkrB+aUlGQrOiUX5OZl5EOWWhgaGKGqQbMCi3NDQ0AhTBll/nj5QZSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Member Number" = _t, #"Preference 1" = _t, #"Preference 2" = _t, #"Preference 3" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Member Number"}, "Preference", "Location"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Preference ","",Replacer.ReplaceText,{"Preference"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Preference", Int64.Type}})
in
    #"Changed Type"

See if a matrix visual gives you better insights.

see attached