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?
Sorry for the delay in adding this ... I wanted to get my dashboard working so my colleagues could start using it.
And apologies if I'm over-explaining, but to save any newbies (like me) having to search around for the details on how to create new tables and add them into the Data Model for this project, here's how I added the three new tables to manage the tags that were being applied to each News Story.
With your Dashboard file open in Power BI Desktop app, click on Transform Data to open the Query Editor.
In the left hand column of the Query Editor, right-click on the existing table and select Duplicate from the fly-out menu.
You now have a copy of the main table named Site Pages (2). In the new table, select the News Categories column.
Now scroll right as far as you need until you find the new table's ID column. Control-click that column to select that, as well.
Now right click on the header of the ID column and from the fly-out menu select Remove other columns.
You're left with just the News Categories and the ID columns in the new table.
At this point, you might want to think about renaming the new table to, say, NewsCat so you can identify it easily.
The last step of this part of the process is to expand the News categories column, so that the values can be read by the Dashboard visualisations. Click on the arrows icon on the right of the column header, select Label and click OK.
Repeat the process for any other Choice columns you need to include in your Dashboard. When that's done, close the Query Editor. Now, in the Home view of your dashboard file, click on the Model View icon, and join the tables (ID > ID) as shown.
Final bit of prep work for me was to create a Measure called "idCount" in the main Site Pages table:
idCount = DISTINCTCOUNT('Site Pages'[ID])
Now you can add, for example, a pie-chart visualisation to the Dashboard. Drag the News Categories.Label column from the NewsCat table to the Legend field, and the idCount measure to the Values field, and that should show you accurately how many times tags from each of the three columns have been added to news stories.
I hope that's been helpful to someone.
TBH, I still don't understand any of that, and wouldn't know where to start with a OData/Rest query or reshaping the data in a proper many-to-many structure. And, I dare say, that other less-experienced Power BI users like me wouldn't either.
As it turns out, while continuing to search for a solution elsewhere, I found that there was a way to get the pie-charts to show results that actually matched what I was seeing in the SharePoint source library.
First, I created a new Measure, called it idCount, like this:
Then, I added the pie-chart visual to the page, setting the Legend to News categories.Label (the expanded column) and its Value to idCount. And, it works.
When I highlight the "Assistants" story in the table listing, it shows the right tags in the News Categories visual (and in the others that I set up, similarly).
I'm still wrestling with the Average Number of Tags panel (so my news editors can get an overview of how much they're tagging, and whether there's too few or too many tags going on). What I have at the moment is a Measure (tagsAverage) that calculates the number of tags, like this:
I'm still trying to figure out how to turn that into an average across all the news stories for a selected date range. I'll probably get there eventually.
I hope this helps someone who's been battling a similar problem.
Sorry for the delay in adding this ... I wanted to get my dashboard working so my colleagues could start using it.
And apologies if I'm over-explaining, but to save any newbies (like me) having to search around for the details on how to create new tables and add them into the Data Model for this project, here's how I added the three new tables to manage the tags that were being applied to each News Story.
With your Dashboard file open in Power BI Desktop app, click on Transform Data to open the Query Editor.
In the left hand column of the Query Editor, right-click on the existing table and select Duplicate from the fly-out menu.
You now have a copy of the main table named Site Pages (2). In the new table, select the News Categories column.
Now scroll right as far as you need until you find the new table's ID column. Control-click that column to select that, as well.
Now right click on the header of the ID column and from the fly-out menu select Remove other columns.
You're left with just the News Categories and the ID columns in the new table.
At this point, you might want to think about renaming the new table to, say, NewsCat so you can identify it easily.
The last step of this part of the process is to expand the News categories column, so that the values can be read by the Dashboard visualisations. Click on the arrows icon on the right of the column header, select Label and click OK.
Repeat the process for any other Choice columns you need to include in your Dashboard. When that's done, close the Query Editor. Now, in the Home view of your dashboard file, click on the Model View icon, and join the tables (ID > ID) as shown.
Final bit of prep work for me was to create a Measure called "idCount" in the main Site Pages table:
idCount = DISTINCTCOUNT('Site Pages'[ID])
Now you can add, for example, a pie-chart visualisation to the Dashboard. Drag the News Categories.Label column from the NewsCat table to the Legend field, and the idCount measure to the Values field, and that should show you accurately how many times tags from each of the three columns have been added to news stories.
I hope that's been helpful to someone.
Helpful resources
Power BI Dataviz World Championships
Vote for your favorite vizzies from the Power BI World Championship submissions!
Join our Community Sticker Challenge 2026
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Power BI Monthly Update - January 2026
Check out the January 2026 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
| User | Count |
|---|---|
| 56 | |
| 52 | |
| 45 | |
| 17 | |
| 16 |
