Easiest Way to Change Sources? (Using new excel sheets for a previous BI model)

[psimp]

I want to send a MPBI model as a .pbit to another employee in my organization, and have him/her use their own Excel sheet as the data for the BI model I have built (using Power BI Desktop). In other words, keep the dashboard structure, formatting, calculations, everything, etc in BI and just swap out the data with a different excell sheet so the model functions with their new data. The new source (Excel sheet) has the same amount of columns, all with the same header names and looks exactly like the origional source, just with diferent data and more rows. I have watched some source change videos, but every time I try it gives me multiple errors. 

What would be the best and easiest approach to do this?

Thanks!

[v-yingjl · Accepted solution]

Hi @psimp ,

1. For creating a pbit file, you can refer: Create and use report templates in Power BI Desktop 

2. To change data sources directly, you can do it in data source settings for the .pbix file:

3. If you have many files with the same structure, you can create a list parameter in Power Query edtior and a csutom function changed a little from @ AlexisOlson mentioned which you can quote it to change the file path:

(A as text) =>
let
    Source = Excel.Workbook(File.Contents(A), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

If the Excel files are indeed the same structure, then you should just need to change the file path in the query editor to point to a different Excel file.

For example, in the Advanced Editor, the query to the Excel file might look like this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\aolson\Downloads\Example.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 To update the source, replace "C:\Users\aolson\Downloads\Example.xlsx" with whatever other file path you want.