In Part 1 of the series Integrating SharePoint Online and Power BI, we saw how we can create interactive reports in Power BI and easily embed them in SharePoint Online.
In this post, we are going to continue with the Power BI report, created and published earlier to SharePoint, using the Purchases List.
In this example, we will add another SharePoint list called Purchase Budgets and create a relationship between Purchases and Purchase Budgets, so we can report on budget variances. After creating the report, we will then set a new custom theme and change the default Power BI color theme of the report and finally, we will configure a scheduled data refresh of the data coming from our SharePoint Lists.
If you would like to follow along, I would recommend reading an earlier post on creating a Power BI Report and embedding in SharePoint Online.
The first step is to Create a SharePoint List named Purchase Budgets and create the following custom columns. You can also set the columns Year and Quarter to be calculated columns and have it automatically calculated using the Created Date.
SharePoint List Columns (Purchase Budgets)
Purchase Budget (Single Line of Text)
Budget Category (Choice)
Year (Single Line of Text)
Quarter (Single Line of Text)
You can use this example to connect multiple lists that have related data.
In Power BI Desktop, click Edit Queries under the Home Tab to enter the Query Editor. Click New Source, then expand the selections by clicking More. Click Online Services and select SharePoint Online List.
Select the Purchases Budgets list and click Ok. Click Choose Columns in the ribbon and select Title, Budget Category, Amount, Year and Quarter.
Change Budget Amount to Fixed Decimal Number
Now we will create a Budget key so I can create a relationship with Purchases. We will use the Year, Quarter and Budget Category to form a unique Key in Purchase Budgets.
Click the Add Column tab in the ribbon and select Custom Column.
Rename the custom column to Budget Key. In the formula enter [Year] & “-” & [Quarter] & “-” & [Budget Category]
Click OK to save the custom column.
We will select the Purchases table in the next step. Click the Add Column tab and Highlight the purchase Date column. Click the Date icon in the ribbon, select Year and then select Year once again. This automatically adds a custom column Year calculated from the Purchase Date.
The next step is to add the Quarter. Highlight the purchase Date column. Click the Date icon in the ribbon, select Quarter this time and then select Quarter of Year. This automatically adds a custom column Quarter calculated from the Purchase Date.
We could also manually add our own custom calculated column in Power BI calculating the Year and Quarter, but Power BI makes this easy for this us. We were able to do this because we had set the Purchase Date column to a Date/Time Field datatype.
Exit the Query Editor by clicking Close and Apply under the Home tab in the ribbon.
In the Fields Pan, click the ellipse near Purchases and click New Column. Rename the column to Budget Key and enter the formula.
Year([Purchase Date]) & “-” & Quarter([Purchase Date]) & “-” & [Purchase Category]
Now we have a Budget Key in both the Purchases and Purchase Budgets tables.
Click the Relationships shortcut in the left quick launch (black bar) where we see the two SharePoint lists that we imported earlier. Drag the Budget Key from the Purchases table and connect it to Budget Key in Purchase Budgets.
Click the connector between the two tables to edit the relationship. Make sure the cardinality is set to ‘Many to one’ and Cross filter direction is set to Both.
Click OK to save the relationship changes. Now we are ready to create a new report in Power BI using data from both SharePoint lists.
Now we will add another measure to calculate the Budget Variance. We will subtract the Budget from the Purchase Budgets list and the Purchase Amount from the Purchases list.
In the Fields Pane, click the ellipse near Purchases and click New Measure.
Budget Variance = Sum(‘Purchase Budgets'[Budget Amount] ) – Sum(Purchases[Purchase Amount])
Creating a New Page in the existing Report
Create a New Page by clicking the yellow + icon at the bottom and rename the page to Budgets.
Select Year and Quarter and set the visualization type to the slicer. This acts as our filter. It set the default filter to the Year 2017 and Quarter 4.
Add a New Table including the Purchase Category, Budget Amount, Purchase Amount and Budget Variance.
Create a Stacked Bar Chart and add the Budget Variance to Value and Purchase Category to Axis.
Create a Clustered Column Chart to compare the Purchase amount and the Budget amount and add the Purchase Category to the Axis.
We can now save the report and then publish it to Power BI Service. We were able to easily take two SharePoint lists and model the data to create a Power BI report. You can use this as an example to build out more complex reports using multiple SharePoint lists.
Customizing the Theme of the report
The report themes feature is in preview and has to first be enabled in Power BI Desktop. To enable report themes in Power BI Desktop. Select File, then click Options and Settings, click Options and then Preview Features.
Select the checkbox ‘Custom Report Themes’ to enable report themes in Power BI Desktop. Click OK to save.
To apply a Report Theme to your Power BI Report, Click the Switch theme under the Home tab and select Import Theme.
You can download a selection of Report themes from the link below –
In this example I will use the Waveform.json theme. Copy the waveform.json theme from the zip file, and select this file as the file to import into Report theme. You can revert back to the default theme by selecting the Default Theme under Switch Theme. Publish the Power BI Report.
You can also create your own custom json theme file. Set the name of your theme on the first line. You can set the hexacode color codes under data colors and the codes for background, foreground and tableAccent. Save it as a .json file format and import it into Power BI.
“dataColors”: [“#31B6FD”, “#4584D3”, “#5BD078”, “#A5D028”, “#F5C040”, “#05E0DB”, “#3153FD”, “#4C45D3”, “#5BD0B0”, “#54D028”, “#D0F540”, “#057BE0”],
Note: If you are not comfortable create the theme file manually, you can use a Power BI Report Theme Generator to pick the colors and automatically create this theme file for you.
Scheduling Refresh of the Report Data
You can schedule a refresh of your data, set the frequency and time slots to refresh your Dataset pulling from SharePoint Lists.
Login to Power BI Service, Open the App workspace where you saved your report. Click the Purchases Dataset and click Schedule Refresh.
For Authentication Method, select OAuth2 and sign in with your Office 365 credentials.
Set the Toggle ‘Keep your data up to date’ to On. Set the Refresh Schedule to Daily or Weekly. Set the Time zone and Click Apply. You can also get notified if the refresh job failed. Add a new Purchase to your purchase list and view the change on the embedded report on SharePoint Online.
To summarize, in this post we created a Power BI report using multiple SharePoint lists. We also created custom themes for our reports, branded them using our custom theme in Power BI Desktop and then published them to the Power BI service. Finally, we scheduled a data refresh, so when the data in our SharePoint list(s) change, Power BI refreshes the data as well.