Power BI is a good way of display results from inside SharePoint in a dashboard/report. I’m using Power BI desktop to set up my report, which can be downloaded from the Microsoft Site: https://powerbi.microsoft.com/en-us/desktop/
If you have ever imported data from SharePoint, you might have noticed that the Managed Metadata columns do not get pulled through the way you want for reporting. You end up with Id’s and GUIDs. This guide will show you how to bring in the term label and how to setup your Power BI report so that you can slice the data on Managed Metadata.
The Term Store Management tool (Which can be found in Site Settings -> Site Administration -> Term Store management, for a site or in the SharePoint admin centre for global) holds your TermStore, Groups, TermSets and Terms.
In the screen shot above, I have:
- Term Store called Taxonomy_IC4zNx….
- Term Group called CannonFodder
- TermSets called Age Ranges, Animation Types, Broadcasters etc.
- Terms which you can see underneath Countries.
For my demo I have created two TermSets called Countries (which you can see above) and Genre.
I have a SharePoint list that I have created using data from movie information from a website called The Numbers. (Ref: http://www.the-numbers.com/movies/ ). My list has 4 columns:
- Title – Single Line of Text (Title of movie)
- Total Worldwide Box Office – Currency
- Genre – Managed Metadata
- Country – Managed Metadata
I have added 5 items of data, taking the top ten from 5 different countries. Example screenshot below.
Power BI setup
After downloading an up to date copy of Power BI desktop and installed it, when you open it, first thing you need to do is get some data. So click Get data
After you have clicked Get data a dialog will appear and you are given a choice of data to connect to. About half way down the list, there is a connection to SharePoint List, select it and then click Connect.
Enter the Site’s URL in the dialog. If prompted, please sign in with your organisational account. After you have signed in successfully, another dialog will appear with a list of all the Lists and Libraries available on the site. (including the hidden lists). I have selected the Movies list. I have also selected the TaxonomyHiddenList, this is very important as this is where all your taxonomy terms are stored for the site that have been used within lists. After selecting your two lists, click Edit. Here we are going to do some tidying first with the data before starting to create reports.
The query editor in Power BI, allows you to manipulate data. Here I would remove columns that I’m not going to be using, ensure the columns have been brought in using the right data type.
With the Movies list first, by clicking on the Choose Columns button on the ribbon, I can select that only the following columns are used.
I have selected just the columns that are displayed in my SharePoint list and deleted all the others. Click OK
You should now be seeing a screen similar to mine below. You will notice that Genre and Country are displayed as records
By clicking on the expanding arrows next to Genre/Country a pop-out will show. This allows you to expand this column to show the columns found within the record. You only need to have TermGuid ticked here. However, to explain my point further that Power BI shows only ID’s and GUID’s I will select all for my screen shots. By keeping the Original Column Name as a Prefix, this will help me identify which columns are for Genre and when I repeat the process for Country which columns are for country.
After expanding both columns you can see that the column label, where you would have expected an English word, is actually a number.
On the left-hand side of the screen where the Queries are, right click TaxonomyHiddenList and create a Reference from it. Do this twice.
By right clicking on the TaxonomyHiddenList (2), rename this to Genre, and with TaxonomyHiddenList (3), rename this to Country. The reason why I have created 2 references instead of 1 duplicate, is so that I can always have the full TaxonomyHiddenList available to me. The two referenced tables will be filtered to only bring back values for the corresponding TermSets.
Now I need to know the TermSetId for Country and Genre. This can be easily obtained directly in your SharePoint site in the Term Store Management Tool. Find the TermSet called Country and click it, then under Unique Identifier you can find the TermSetId. Take a copy of this for both Country and Genre.
Back in Power BI, in the Query for Country first, find the column called IdForTermSet. Click the dropdown button, and you want to select the GUID that matches to the Unique Identifier you found in the last step for Country. Click OK.
Your query will now only show the rows for the given TermSetId, in my screenshot below this is for Country. I then repeat in the Genre query for Genre.
Then for both Country and Genre I remove all columns apart from Title and IdForTerm. Use Term<LCID> instead of Title if you are displaying from a different language.
Lastly, I rename the Title columns to Genre and Country in the corresponding query. Then I click the Close & Apply button from the ribbon bar.
The next step is configuring the relationship between the TermSet table and the Movie table. At the left hand side of the page, select the Relationship icon. You can see from the screen that Power BI has tried it’s best to match up your relationship, however you will notice that it is incorrect.
Click on Manage Relationships in the ribbon bar. On the Mange relationships dialog, delete both relationships that are currently there. Then click New to create a new relationship. The first table you should select, would be Movies, then click on the column called Country.TermGuid. Then for the second table, select country, and click the column called IdForTerm. Set the cardinality to Many to one, and I’m going to set the Cross Filter direction to single. (Note:
If you se the Cross Filter direction to both, when you use slicers for Country and Genre later on, it will filter the other slicer to only show the values left in the current data set)
I’m am then repeating the process for Genre. Then Close the Manage Relationship dialog.
Now I have set up the relationship correctly, when I want to display the Country/Genre, I can pull the information from the Country or Genre list. For example, I’m adding a Table to my report. I drag in Title and Total World Wide Box Office from the movie table. Then I drag in Genre from the Genre table, and Country from the Country Table.
I can also add two slicers to the page, one is Country, and the other is Genre. I can then filter the data further, in the below screenshot, I’m filtering by United Kingdom and Thriller/Suspense films.
There is plenty more I can do with this data now, now that I have the correct label terms for Taxonomy/Managed Metadata columns in my Power BI.