Power Bi Desktop changing data source of excel files in power bi

[pankhuri49]

Hi 
I am new in Power Bi. We have created a power bi report which uses multiple excel files. These excel files at month end are updated by end users.
To smooth the process we are planning to merge excel files in to one file with multiple worksheets so that only one excel file can be  shared to the user.
How can we change the datasource from individual excel file to worksheets in one excell file. we dont want to report or graphs to get affected.
Please help

[Anonymous · Accepted solution]

Hi @pankhuri49 ,

I think you have kept the column name to be same after you merge your excel files, so you need to try to change the data source and sheet name in your Power Editor Query by below step:

When you click the table in Power Editor Query, you will see below steps, right?

The steps you need to change are "Source" and "Navigation"

Click "Souce", you will see :

= Excel.Workbook(File.Contents("YOUR_EXCEL_ROUTE"), null, true)

Change all the excels you used to the merge file ROUTE

Click "Navigation":

= Source{[Item="YOUR_SHEET_NAME",Kind="Table"]}[Data]

Change the "Item" for every excels you used to the new SHEET NAME in your merge file

Please try

Aiolos Zhao

[amitchandak]

Refer, if this can help

https://exceloffthegrid.com/power-query-source-data-location/

https://powerbi.tips/2016/05/query-editor-editing-m-code/

https://www.inogic.com/blog/2019/11/how-to-modify-power-bi-report-data-source-queries-from-advance-e...