I WOULDN'T NORMALLY PRESUME TO OFFER ADVICE ON POWER BI, as it isn't my core skill. However, I was working on a dashboard to help my colleagues in Corporate Comms analyse the views-by-category of their Intranet news stories and ran into some unexpected challenges.
Those working in corporate environments will be familiar with the ideas of company objectives and employee behaviours, and we had been tagging our news stories with those values for some time (using custom columns added to the newshub's Site Pages library), but hadn't really tried to break down views and time-on-page by any of those metadata values. What complicated it massively was that it was possible for my Corporate Comms colleagues to tag stories with more than one value for Company Objective (for example).
So when I started setting up the dashboard, using existing data from the last 12 months, I was seeing some unexpected behaviour.
 |
| When I selected a single story in the dashboard, Power BI was telling me that there were 12 tags associated with that item, yet I could see from the SharePoint SitePages library that the item actually had only seven tag values. |
In my limited experience of setting up other dashboards I knew that some SharePoint columns return a value of "Table", when viewed in Power BI Query Editor. All I had to do was expand those columns and we'd be on our way, right?
 |
| Because some SharePoint columns, like Choice, generate a Table rather than a Value, this is what you see in Query Editor. |
But it wasn't that simple. After I had expanded the column, I found it was returning those phantom values to the dashboard.
 |
| Clicking on the little icon in the right of the column header brings up this panel. Selecting Label and clicking OK expands the column to show the actual Values. |
Where had the five extra values come from? I was having real trouble figuring it out. Like I say, Power BI isn't my day job, so this was a knotty problem for me.
 |
| This is what the metadata columns looked like for that one item after expanding in Query Editor. There are 12 rows here when there are only a total of seven metatags applied to the item in SharePoint. What in the hootin' heck was going on? |
Figuring that the world is full of smarter people than me, I posted the question in one of the Microsoft User Forums and waited to see if anyone could help.
THE SOLUTION
The Microsoft Community were very quick to explain that what I had ended up with by expanding the Choice columns the way I had was a Cartesian join. Instead of adding the tags from the three columns, it multiplies them. So two News category values, three DNA values and two Strategic priority values give me twelve instances of metadata tags.
2 x 3 x 2 = 12
Instead of expanding each of the columns, what I needed to do was create three separate Tables in Query Editor - one for each of the metadata columns - and connect them to the main table. Then I had to create a Measure to count up the metadata tags in a non-Cartesian way (we'll get to that). That was helpful, but still lacking a bit in detail. After managing to fill in the gaps in the process, I thought I'd post an account of how I did it here to save anyone else hours of fiddling to reach the same result I did.
With your Dashboard file open in Power BI Desktop app, click on Transform Data to open the Query Editor.
 |
| Click the image to enlarge. |
In the left hand column of the Query Editor, right-click on the existing table and select Duplicate from the fly-out menu.
 |
| Click the image to enlarge. |
You now have a copy of the main table named Site Pages (2). In the new table, select the News Categories column.
 |
| Click the image to enlarge. |
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.
 |
| Click the image to enlarge. |
Now right click on the header of the ID column and from the fly-out menu select Remove other columns.
 |
| Click the image to enlarge. |
You're left with just the News Categories and the ID columns in the new table.
 |
| Click the image to enlarge. |
At this point, you might want to think about renaming the new table to, say, NewsCat so you can identify it easily.
 |
| Click to enlarge. |
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.
 |
| Click the image to enlarge. |
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.
 |
| Click the image to enlarge. |
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.
 |
| Click the image to enlarge. |
OPTONAL EXTRA
The other feature I added was a panel in the Dashboard that showed the average number of metatags that had been added to news stories - both by story and by metadata column. The earlier version (see the screengrab at the start of this entry) had been returning the incorrect, Cartesian values. To get it to return the correct values, I needed to add some new Measures to the Dashboard.
First, I had to count the metatags, which required a slightly different approach. Here's the script for that Measure (TagsCount):
TagsCount = VAR NewsCount =
CALCULATE(
DISTINCTCOUNT('NewsCats'[News categories.Label]),
ALLEXCEPT('Site Pages', 'Site Pages'[ID])
)
VAR StrategicCount =
CALCULATE(
DISTINCTCOUNT('StratPrior'[Strategic priorities.Label]),
ALLEXCEPT('Site Pages', 'Site Pages'[ID])
)
VAR DNACount =
CALCULATE(
DISTINCTCOUNT('DNA'[DNA.Label]),
ALLEXCEPT('Site Pages', 'Site Pages'[ID])
)
RETURN
NewsCount + StrategicCount + DNACount
 |
| Click the image to enlarge. |
Then I created another Measure (TagsAverage) to calculate the Average Tags per story, which takes the output from the TagsCount Measure and calculates the average I needed:
TagsAverage = AVERAGEX(
VALUES('Site Pages'[ID]),
[TagsCount])
 |
| Click the image to enlarge. |
The final task was to update the Card visual displaying the Average Tags per story with the Measure TagsAverage. And now the Card was displaying the correct count of seven - which is what I'd wanted all along.
 |
| Click the image to enlarge.. |
All-in-all, not terribly intuitive, but the usefulness of the final Dashboard made the challenge worthwhile.
 |
| Click the image to enlarge. |
I hope that's been helpful to someone.
