How do I show instances of values from a SharePoint Choice Column as a pie chart in dashboard?

[Mister_A]

I'm trying to build a dashboard that will display info about the metadata tags on news stories in our SharePoint Intranet. The NewsHub is a subsite, each news story is a page and we have three custom choice columns where we tag each story with a value (or values) for News Categories, Strategic Priorities, DNA Behaviours.

I want to show three pie charts that show how many times tags from each of the three columns have been added to news stories. I thought this should be really simple, but it has been anything but ...

The story about "Assistants come together" has a total of seven metatags, but the dashboard is counting 12.

So, in QueryEditor, the three metadata columns looked like this:

What you'd expect, right? I couldn't just whack one of those columns into a pie chart because it doesn't work like that. So my first effort was to Expand the metadata columns to separate out the values ... 

Added the pie chart to the dashboard, populated it with the expanded News Category column ... but something wasn't right. In expanding the metadata column, it had added duplicated rows to the table behind the scenes, and the News Category tags were being counted multiple times. This increased geometrically when I expand the other two columns.

So I ended up with 12 rows for one news story and the metadata tags were getting counted multiple times.

My question is: how do I get the pie charts to count the metadata tags just once for each news story, so my news editors can see how many stories they've applied a particular tag to?

[Mister_A]

Forgive me ... but I was looking for a solution. Are you saying there isn't one?

[lbendlin]

- Power BI only understands scalar data types, not objects.

- expanding fields in Power Query is very expensive.  Better run an ODATA query against your sharepoint with pre-expansion

- if you can have multiple tags per story then you are looking at *:* relationships. You may need multiple visuals that each look at your data from different perspectives.