7 METHODS to change source data location in Power Query | Excel Off The Grid

[@shoppersdream]

Very Nice, thanks! I was just changing it in the Source and my other queries were not changing. I didn't know these 7 methods. Thanks

[@kenmcmillan2637]

Thanks for the review Mark. My goodness, you have an incredibly clear camera!! Super sharp image!!

[@ExcelOffTheGrid]

Thanks. Not sure why, it’s the same camera as before. I am trying to up my game. Will be using a new camera and monitor next year, so we will see how that improves things further.

[@timlucas4155]

I've started using a VBA File Dialog Approach. Splitting the path into one cell, file name in second. These are named ranges that PQ reads from to get the path. I have then had PQ do the retrieval and finally the used Python linked to the data imported dynamically via PQ.

[@sankethingne]

i wasted my a lot of time to find how to do this and this video within minutes directed to the point solution.....fantastic.... 😊

[@ExcelOffTheGrid]

Glad it helped! 😀

[@garyrowe58]

Me too! It's disheartening to have to spend so much time searching until you a) find the right term(s) to search for, and then (b) find the right video! The only upside is that you learn lots of other things (hopefully usefull) during the search ;-)

[@mojtabareyhani7905]

Thanks a lot for your great tutorial. Please create a video about how can create a flexible and reusable power query structure.

[@ExcelOffTheGrid]

I was thinking about this yesterday. I'm not sure anybody has done a video about that. I'll add it to my list of possible topics.

[@IvanCortinas_ES]

Excellent tutorial Mark. Thank you for such interesting content.

[@ExcelOffTheGrid]

My pleasure!

[@jamesregan4791]

This is a perfect tutorial on changing the data source, after seeing 6 this one is direct to the point on changing data sources thank you.

[@iankr]

These are all sources on a network share. It's a whole lot more fun if you're working with files stored on SharePoint, as companies are increasingly doing these days. Microsoft needs to make this much simpler!

[@ExcelOffTheGrid]

Agreed. I think the same principles apply for SharePoint or Network drives. The challenge is getting the file path to change it to, as we need to look in the "secret" information location 😂

[@rigobertogarcia]

Great, great and more great Mark. Thank you very much for your contributions that feed our professional development. Thank you

[@ExcelOffTheGrid]

You’re welcome 😀

[@chriswall4795]

The excel named range can be adapted to pick up where file is saved and pass that to PQ. Then data can be in a subfolder called data relative to where the PQ file is ... Your methods all rely on hard coded paths. We break them into serverpath projectpath and filename parameters then concatenate them in PQ for greater flexibility.

[@ExcelOffTheGrid]

For my projects I follows a similar method of including different parts of the string. I tend to concatenation in Excel rather than in PQ, but it’s the same approach.

[@Sumanth1601]

Excellent content.. Good for beginners.. however the one I switched to using the most is under the Data ribbon > Get data > Data source settings. I don't have to go to power query window.

[@ExcelOffTheGrid]

Yes, that's a good one. Similar to #4. My favorite is the cell based parameter, then I don't even need to open up anything. It takes a few extra seconds to get around the Formula.Firewall error, but then it's as easy as typing text into a cell.

[@abuibrahim5178]

Thanks for tutorial sir Please answer my question sir What happened if I just rename the data source? Is there changing the data and all the stepped I run in my power query?

[@ximox]

Hi Mark, how you recommed to use a path_parameter of folder to open at once all the file in that folder.

[@ExcelOffTheGrid]

I would use this method: kzbin.info/www/bejne/q4Gzn2Wbn9SNZ9E

[@dean.beenaliza]

How about changing the source from another workbook to the data in the current workbook which has the same raw data table?

[@ExcelOffTheGrid]

From Power Query’s perspective that is a different connector. So you’ll need to re-create the initial steps, then build an if statement which uses the different connectors depending on the scenario. Possible, but definitely some M code required for that.

[@therevenant211]

Will these methods work if the issue is that other users cannot refresh the file? For example, I have 3 different data sources driving queries in a tool that I need to make usable for my employees. But none of them can refresh the file because the filepath has my userid in the filepath. So would I need to create a named cell that contains a filepath that adjusts that userid section of the path depending on who is doing the refreshing, or am I overthinking it?

[@CASSIE_COLE]

What if I want to change the source from an external file's table to a table inside my document?

[@ExcelOffTheGrid]

You’ll need to make changes to the M code. Create a new query to connect to the file, then take the parts of the code you need and insert into the original query. Make sure you take a backup. It can be a bit daunting, and very error prone if you’ve not tried to work with M code before. The last option is the rebuild the query from scratch. But try to avoid doing that if you are able to.

[@arungupta8444]

Hi, What if the Source File is used for the Merge + Append + Group transformations with other files . but now the source file got Updated with Lots of Data and it is difficult to update the file and we need to replace the source file alltogether, with new file with same name, without affecting the previous output. Can it be done with "Parameters" Setting or something else. Please Advise.

[@YWang-gr3yp]

I followed exactly your steps, but when I go to data source seting, the "Change Source" button is greyed out, please advise, thanks a lot!

[@dianat5710]

In my power query, the source is a separate excel worksheet. How can I change it to an online source? I need it to connect directly to a salesforce report. I haven’t been able to find out how to change this. Please please please let me know if theres a way!! Thank youuu!

[@ajayrathod7777]

How to create this for two excel files,one is for source excel file and other is mapping excel file