How to visualize count of list components in column

[TJohnson6754]

Hi there,

I'm newer at this and need help with what seems like it should be simple but hasn't been (for me) so far:

My table contains a column that contains list data.

Example data: 

ID	List
1	dog,cat,iguana
2	horse,dog
3	cat,hamster
4	horse,iguana
5	horse,cat,hamster

I want to be able to visualize the data in Power BI similar to this:

What I've tried:

• I made measures, each measure counts one component in the column, then I'm unable to add the measures to visualize them as separate bars on the bar chart.
• I've made separate columns for each list component, each one with a 1 or 0 depending on whether that row contains the list component of interest.
• I tried using dax to make new tables, one for each list component, with the intention to append them to one another, but I wasn't able to append these types of tables in "Transform Data". 
  • PetsDog = SELECTCOLUMNS(FormResponses,"Pet",if(CONTAINSSTRING(FormResponses[List],"dog"),"dog",""))​
  • PetsCat = SELECTCOLUMNS(FormResponses,"Pet",if(CONTAINSSTRING(FormResponses[List],"cat"),"cat",""))​
  • etc.

Is there a simple (or complicated) way to do this?

[alekhved · Accepted solution]

hi @TJohnson6754 

I created a temp table just like the table shown above and followed the certain steps in Power BI Query editor to create a new table which could help solve your problem.

Temp Table

Steps followed in Query Editor

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrJT9dJTizRyUwvTcxLVIrViVYyAgpn5BcVp+oAJcEixkARkKKMxNziktQipdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, List = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"List", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "List", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"List.1", "List.2", "List.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"List.1", type text}, {"List.2", type text}, {"List.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
#"Removed Columns"

Output at end of all steps

Again, this solution is based on assumption that you have  "," as delimiter. And you are not much worried about multiple records with the same ID (different list name though)

Hope this helps!!

alekh

[TJohnson6754]

This worked for me, thank you so much!