Create A Power BI File Template For Different SharePoint Site Every Time

Overview

 

In this article, we will talk about how we can create the Power BI report using the SharePoint data sources and generate the template of the created report where the SharePoint Site URL is configurable. Once we create the template of Power BI Desktop file, we can export the file as a template. Then the end users can generate the report based on any configurable SharePoint site with a different set of data.

 

Example

 

We have the following two site collections with the same structure of the List data.

• I have a list named “R1” in my first site collection which is “HR”.
  
  
  
  
• I have a list named “R1” in my second site collection which is “subHR”.
  
   

Assume that “R1” list is available in other 50 sites or site collections with the same list schema. The data would be different on every site.

 

The client is looking to generate a different Power BI report for every site collection from one generalized template where all the charts are already created.

 

User will open the template file and add their credentials and generate the report automatically. They will save the report in their local machine and publish the report to their own “My Workspace”.

 

So, now let’s get started with the step by step procedure!

 

Step 1

 

Load the data to Power BI Desktop from SharePoint Online.

1. Click on "Get Data" from Home.
   
   
   
   
2. From Online Services, select “SharePoint Online list”.
   
   
   
   
3. Enter URL
   
   
   
   
4. From Authentication, select Microsoft Account. Click on the Load button.
   
    

Step 2 - Add Manage Parameter

 

From Home, click on Edit Queries,

1. From Home, Click on "Manage Parameters" and select New Parameter.
   
   
   
   
2. Add the following information and click on OK button.
   
    

Step 3

 

Change the Source by List Name from Manage Parameters,

1. Click on Advanced Editor
2. Below is the OOTB code which is generated for the List Connection by ID.
   

   let
   Source = SharePoint.Tables("https://dshah28.sharepoint.com/HRMS/", [ApiVersion = 15]),
   #"0d5ba730-f5c3-4226-9ec6-0623c2540102" = Source{[Id="0d5ba730-f5c3-4226-9ec6-0623c2540102"]}[Items],
   #"Renamed Columns" = Table.RenameColumns(#"0d5ba730-f5c3-4226-9ec6-0623c2540102",{{"ID", "ID.1"}})
   in
   #"Renamed Columns"

   
   
   
3. Change the code to the following.
   

   let
   Source = SharePoint.Tables(SiteURL, [ApiVersion = 15]),
   #"R1" = Source{[Title="R1"]}[Items],
   #"Renamed Columns" = Table.RenameColumns(#"R1",{{"ID", "ID.1"}})
   in
   #"Renamed Columns"

   
   
   
4. Click on Close and Apply.

Step 4 - Generate Power BI Template

1. Go to File menu, select Export > Power BI template
   
   
   
   
2. Add the template Description.
   
   
   
   
3. The template has been saved successfully.

Step 5 - Test the Template file

1. Double click on the template file.
2. Select the Site URL which you to wish to configure.
   
   
   
   
3. Add credentials. The report has been generated successfully.

Conclusion

 

This is how we can create a template file for the SharePoint data source. Isn’t it amazing?

 

Stay connected with us for  more of these amazing articles!

 

Happy Reporting!!