Power BI template with SharePoint hosted excel

[Tom-ADV]

Hi,

I have a Power BI report which I want to convert into a template, the data source is an Excel file from SharePoint,

the tables in the file are gurenteed to be in the same format including column names.

How can I load a diffrent table to the template without having to do table transformation all over again each time

I start a new project form the template ?

I found a solution for local files but it doen't work with a SharePoint file path:
https://www.youtube.com/watch?v=x1MOX7GSx9g&t=568s

Are there any solutions ? 

[Idrissshatila]

Hello,

Check this link that helps in connecting data from sharepoint into power BI.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-sharepoint-online-list

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin

[TomasAndersson · Accepted solution]

Hi!
If you go to the model view, right click on the data source, select "Edit query" and then go to "Advanced editor" you can see and edit the Sharepoint path to your new file. All transformations in laters steps should then be the same.

[connect]

Yes, there are several ways to load a different table into a Power BI template without having to do table transformations each time you start a new project from the template. Here are two possible solutions:

1. Parameterize the data source: You can create a parameter that specifies the path and name of the Excel file in SharePoint. Then, you can use this parameter in your Power Query queries to dynamically reference the file. To do this, follow these steps:

• In Power BI Desktop, go to the Home tab and click on the Manage Parameters button.
• Click on the New Parameter button and enter a name for the parameter (e.g., ExcelFilePath).
• Set the Data type to Text and enter the default value for the parameter (e.g., the path to the first Excel file you want to use).
• Click OK to create the parameter.
• In the Power Query Editor, select the queries that reference the Excel file and replace the file path with the parameter name (e.g., replace "C:\MyFiles\ExcelFile.xlsx" with "@ExcelFilePath").
• Save and close the Power Query Editor.
• When you create a new project from the template, you can change the value of the ExcelFilePath parameter to reference a different Excel file in SharePoint.

1. Use a SharePoint folder as the data source: Instead of referencing a specific Excel file, you can reference a SharePoint folder that contains the Excel files you want to use. To do this, follow these steps:

• In Power BI Desktop, go to the Home tab and click on the Get Data button.
• Select the Folder option and enter the URL of the SharePoint folder that contains the Excel files.
• Power BI will display a preview of the Excel files in the folder. Select the files you want to use and click the Combine & Transform Data button.
• In the Power Query Editor, you can transform the data from the Excel files as needed.
• Save and close the Power Query Editor.
• When you create a new project from the template, you can point the SharePoint folder data source to a different folder that contains Excel files with the same format and column names.