This is gold!!! This was extremely helpful. I don't know how many times I had to change data sources for one reason or another and I had to basically reinvent the wheel with the change steps and relationships.
I learned the hard way to put each data source in its separate query (and duplicate or reference before modifying Applied Steps) then use them as "Source" for other queries. Modulize your queries and re-use them or save as templates. Also make data file path dynamically by creating in the current workbook: folder path, file name, and CONCAT then to make file path name ranges; then import file path in a separate query step.
Thanks for this video Question. This work just for 1 PBI report. What can I do having several PBI reports? All PBI reports are connected to the same data source/Server location and I need to change it to a new Server location. Is there a way to change all data source across all PBI reports rather than open and do it one by one? Thanks
Yes, unfortunately it does matter. The M code in Power Query that you're going to be modifying is case sensitive, in contrast to the DAX language in PBI Desktop is not case sensitive. I would recommend updating your original sources to match before performing this method.
This is great. BUT what if the data source is changing all headers to small letters? And before the headers were written in camel (capital and small)? This breaks my relations and all power query code. Do you have a solution for this?
You would have to modify the M code a little bit. If it's just a column/header name difference, you can just change any reference to those column headers in the M code directly.
There's more than 1 way to do this. Since csv works faster in Excel Power Query, I do this all the time for same usually large data table. I would delete the 1st row of the csv table as a data source. Open the Excel header table (import it or put in current workbook) then merge cvs table into it. That's it. There are other ways if you RU-vid search.
