Change the Power Query source based on a cell value | Data refresh automation | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Download the example file: ★

★ About this video ★
Wouldn’t it be great if the data source in Power Query could be linked to a cell value that we could easily update? Yes, it would! So, in this video, we’re going to look at how to do exactly that. You will learn how to change the Power Query source based on a cell value.

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel #ExcelOffTheGrid

[JB-qcde]

You would think after watching this video so many times I would have it memorized but here I am again using it in another excel file. It is on my top 10 most useful/ watch videos. Thank you for creating it.

[spharaoh-dvfw]

Amazing as always. Knew it was an old one as there's no "if you're ready, let's get started"! Your presentation has come on heaps since these vids :)

[ismaelkourouma]

Thanks Mark. This is the best and quick way to update the file path into Power Query.
I have just amended a bit to have something fully automated by using the following formulas to get the file name and the file path without no manual change:

=CELL("filename") then
=MID(B4, FIND("[", B4)+1, FIND("]", B4)-FIND("[", B4)-1) assuming the fomula above is in the cell B4
=LEFT(B4, FIND("[", B4)-1) assuming the fomula above is in the cell B4

Thats all. Simply but very efficient.

Cheers

[geektimes]

Most helpful video ever. Not even ChatGPT was as clear as you. Thank you!

[markroy]

So far the easiest and simplest method I found in youtube! Thank you!

[alexandrereisonline]

Thanks!!! I used this tutorial and had complete success!!!

[unwrthy]

This is brilliant and helped me from copying and pasting from a bunch of different workbooks. Thank you!!

[timosborn]

I was trying to do this with Parameter queries and then I saw this, great technique, thank you!

[calebgerweck]

Thank you. This is by far the easiest explanation for this.

[reversemoustachecat]

Pure gold!! I have used your tutorial video to figure out how I can update a query web path name using a similar method. Now my data table changes based on the stock symbol entered without the limitations of the office365 data types. Thank you!

[jasminechater]

Thank you, thank you, thank you! I have been looking for this all week!! So simple and useful - thanks a billion :D

[grahamhitchings]

Brilliant!! I used this method to update a stored procdure call based on the values inserted into a couple of spreadsheet cells. Many thanks :)

[CHEMELLE_B]

Works brilliantly! And the video was easy to follow. Thanks so much!

[rogeriopenna]

It worked! Awesome! Will be using this with PowerAutomate Desktop, where PowerAutomate Desktop will be changing the paths and filenames based on another Excel file list.

[pauliboo]

That's far better than using parameters to pull data from the sheet

[ilpol]

Thank you, really useful !!! I applied it to Dynamic Web Query using dynamic URL based on cell values and it works too.

[doublesundaygoku]

Best Solution. No firewall formula error

[peggymahabir]

Sweet! It worked perfectly. A simple and easy to follow tutorial. Thank you!!

[choychaby]

Exactly what i need for my current project requiring flexibility for the file source. Thanks

[jasonjackson]

This is great! I’m going to use this at work right away!