Quickly Change Multiple Queries, Same Source in Power Query

If you've created multiple queries from the same source and then the location of that source changes, you'd think that you would need to go into each query and one by one change the source location. If there's a few queries, it not so bad. If it's a LOT of queries, that will be painful. You can actually use a parameter and then tweak some source settings to let you change the source location all in the same time. Plus at the end of the video I'll show a simple design choice that could be used to mitigate all this without using a parameter.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

[@duncanwil]

Very good, Doug, thanks for sharing at exactly the right time!!

[@DougHExcel]

Glad you enjoyed it!

[@mcwahaab]

I was doing the one by one updating only this week. This is so helpful. Thanks

[@DougHExcel]

Glad it was helpful!

[@SimX9000]

That's awesome! Never used a parameter before. Super helpful. Thanks

[@DougHExcel]

Great to hear!

[@wayneedmondson1065]

Hi Doug. Great tips! Thanks!!

[@DougHExcel]

Thanks Wayne!

[@tlee7028]

Thank you for sharing! How do I change source data file when I dont have the gear icon at Source?

[@user-dn5gd1rn9f]

I was using the reference method, but I read somewhere that Referencing can impact performance. Do you have any thoughts about that?

[@Viego4300]

Thanks for the tips! I have a question tho, I merged many sheets because I needed a column from each sheet. Do you think that i can automate this report by just changing the source once a week? I've tried it but the merging thing doesn't seem to work, the data remain the same and dont update when I change to the new source. Thank you!

[@EmanMerc]

This is awesome!

[@DougHExcel]

Glad you thought so!

[@user-hk9xm7th5f]

Thanks-great tip! Is there a similar hack to change multiple queries when a column is deleted in the original query? I'm struggling because I want to delete some columns in the source, but then have to change multiple steps in each of the dependent queries to remove reference to the column.

[@DougHExcel]

Early step maybe to demote the header column so you get generic column id and do cross lookup to remove. There's some similarities to these vids that might give an idea
czcams.com/video/vfAWSPEyYWs/video.html
czcams.com/video/0eMOKmPi0h8/video.html

[@RafaelLiscanoVera]

and what about if I need to change the current source (local Disck) to a sharepoint like Onedrive per example? bc the only option that I have is change from a local folder to another local folder. no to another web source.

[@DougHExcel]

Sharepoint links are a bit tricky. See this for an idea czcams.com/video/G6LS1y60YgM/video.html

[@BestICan]

Do you need to have a Folder as the source instead of individual files for this method to work for all the queries if they reference different files?

[@DougHExcel]

yes it should, basically this method is referencing the folder and if your other queries have different filenames but "in" the same folder, should work.

[@pavol.cernak]

A very nice idea. However, in most cases, I want users not to change any query (the majority of them even doesn't know what it is), my method is to write a full path to the source(s) inside the sheet, to make a query of it, and finally to reference this query.

[@DougHExcel]

Hi Pavol Cernak, thanks for the comment! Having the path in a sheet as a query is also another great solution, especially if sharing with other's that want to change the source but not wanting to go into the editor.

[@iankr]

Hi
This is very useful. What if it's not as simple as just changing the file path using a parameter? How do I specify the Source *query* for another query using a parameter? For example, I have 2 source queries, say SourceA and SourceB, both connection only. My main query can use either of these queries as its Source, and I want the user to be able to choose which one from the worksheet? I need two Source queries because the different file sources need different transformations done to them.

[@DougHExcel]

This video might give an idea czcams.com/video/lLoqlueXWzU/video.html

[@Oppenheimer1968]

slow down