Graph count of items in a column with multiple items per row

[Anonymous]

Given this type of data:

Suppose I want to make two different graphs, one for the individual count of the items in the Order column and one for the Pets column. What would be the simplest way to do this?

Note that I want to graph the count of each item, and not group of items. This way I'd obtain a count of 4 - Burger, 2 - Pizza, and not 1- Burger, 1 - Pizza, Coke and so on.

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could change “,” to “|” in power query . Then create a new table in Power BI , Finally count of “Order” and “Pets” , the following formula to create :

Step1: Duplicate Order 、Pets columns

Step2: Replace Order 、Pets columns(“,” ->”|”)

Step3:Create new table

NewTable = 
var _a=SELECTCOLUMNS('Table',"order",PATHITEM([Order2],1,TEXT),"pets",PATHITEM([Pets2],1,TEXT))
var _b=SELECTCOLUMNS('Table',"order",PATHITEM([Order2],2,TEXT),"pets",PATHITEM([Pets2],2,TEXT))
var _c=SELECTCOLUMNS('Table',"order",PATHITEM([Order2],3,TEXT),"pets",PATHITEM([Pets2],3,TEXT))
return UNION(_a,_b,_c)

Step4:Create two measures to calculate count

ordercount = COUNT('NewTable'[order])

percount = COUNT('NewTable'[pets])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[ChrisMendoza]

Here's the table so I can come back to this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/LCsMgEEV/RWbtT6SWPhYtWXUTXAzpECRGwSSkzdd3lMYKga686vF6pmngTD50BBIOM6+Bw9F3oGUDlXPI29qsK0qhfB8phVO6vGF450dSnIKhMUPcwPlLVpZev5oE7hD3DLTw6bUloQLhwPmCwzixUARqnG3xW4mlopK9m7aPotlXih0T++Jsm80m8kBL/+cCrT8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Order = _t, Pets = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Order", type text}, {"Pets", type text}})
in
    #"Changed Type"