Single Power Query for Multiple Files with minor change

[Syndicate_Admin]

I have over 150 excel files. All have the same power query code except the source.

When I have to make any changes in the code, I have to edit 150 files. Is there a way to have the same code in all the files except the source and make changes to one that gets copied to the rest automatically?

[Syndicate_Admin]

Hi @SSG0401 

A nightmare situation, are this all active Excel files or are they historic data? Do they all share the same column names including format?

I'm not sure about performance issues for Power BI, but you can try the following if the requirements above are met.

• Create a folder in SharePoint
• Add one Excel file to this folde
• Connect to a SharePoint Folder using the connector. 
• Enter the URL, should look like this https://yourcompany.sharepoint.com/sites/Data 
• This will show everything available on this site
• After loading, find the folder you created by filtering the urls on the right side.
• You should see your file and load it in. This will create a Transform File. Never delete the first file you added as Power BI is reading from this.
• Then you can add the Power Query code that exists
• Next you need to add all the excel files to the SharePoint folder
• When they are all added, refresh the query.
• Any new files need to be added to SharePoint folder and will picked up on the next Report refresh. 

This can also be achieved using Dataflows, which would be probably better for performance of the report, but maybe not for the Service. If the files are all historic, you could set up Incremental refresh, just to refresh the active file

Thanks

Joe

[Syndicate_Admin]

Thanks for the reply Joe!

Yes, these are all active worksheets with same column names and formatting.

I do not see any option in "Get Data" for SharePoint Folder.

Also, how will this work when I have a different URL sources in query editor in all the files and just want to use the same transform after loading the source?

[Syndicate_Admin]

Hi @SSG0401 

The connector is there, you just need to serach 

Without knowing the setup at your company and if it's possible, the Sharepoint folder can be the new source for all files and the Excels are shared individually with each Excel Owner. 

The folder will be the new source. When you load in the first Excel and implement the Power Query Code minus the old source information, as this will be created new when importing into Power BI. The added folders later will be automatically changed to the transformations of the first file added. 

Do some testing, make a copy of 3 of the files and try it out.

Depending on the complexity of the Excels and the licence situation in your company, the best solution here would be a Power App that writes the data to one source, the source could be as simple as a CSV or one Excel or even a SharePoint list.

Thanks

Joe

[Syndicate_Admin]

Hi @SSG0401 ,

Did you use the power query in power bi desktop? If so, you can try templates in Power BI Desktop.

Create and use report templates in Power BI Desktop - Power BI | Microsoft Learn

Or you can use the from folder, and combine all the files.

Combine files overview - Power Query | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.