thaks for this - need to do this for a folder content PQ

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

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

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

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

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

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!

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

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

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

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

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

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

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

This worked out perfectly. Thanks!

Just what I was looking for! Thank you!

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

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

The best and simple way!

So cool! Worked great for me!

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.

Mark - thank you for these videos. They are really extremely well explained and usefull!! I've implemented Your tricks without any problems (works like a charm) and really appreciate for Your help. Well done! Jarek.

I liked your video. That was exactly what I was looking for. Thanks!

Thank you so much! I needed a method to change what the source was at a certain time every night because the live source defaults to the wrong day for a few hours every night. I initially tried a “try/otherwise” method, but it still got an error (“couldn’t convert to number”). This worked great!

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

Brilliant. Really helpful and easy to understand

This is simply a great method.

Best Solution. No firewall formula error

Thanks a lot for most useful PQ trick 👍 Very well explained 🙏🙏🙏

You made my day, thanks and keep posting something new !! :)

Very good explanation. Very useful!

thank you! I have 6 files that I have to import to power query, and have to this manually for the last 4 weeks now. The only difference is there's a diff folder each day, folder names are named after the date ie. 070722. This code makes it easy.

Brilliant, thank you for the simple but most effective method of explaining how to do this, I already have many ways of using this to create great reports.

Great video!!! I use only to point to the folder path and that was exactly what I was looking for!

Thank you, thank you and thank you!

Thank you for the awesome video! It's very very helpful

Very useful, thank you very much 🙏🙏🙏

Thanks so much - very helpful.

It's awesome.. More videos needed In power query topic

Awesome, very simple and usefull

Thank you. it's very helpful. This method also could apply to get data from the web, with parameter/source from cell. I've tried it. Thanks.

Amazing! Thank you a lot!!!

good stuff. Thanks for sharing.

Fantastic explanation !!! Tks

Thank You so much for the simple explanation, Can you please explain a little bit the explanation of the M code, or point me to where can I understand it ?

Big thanks Mark.

Thanks a lot!

That was awesome bro!

Nice & Easy

Thanks a lot 😊

There are multiple ways of soing this. This is the 3rd I’ve seen. Maybe the simplest. Another nice one was a custom M function that returned parameter values based on name. The source was a table in the worksheet.

Works well. Thanks

I've looking for how to do this. Thank you.

Hi Mark. Great video! Question: what if you have multiple files that you use inside your Power Query ( I currently have three)? May I just define the folder instead and not define the file(s)? Thank you and keep up the great work!

Very Impressive!

Brilliant idea. Thanks so much for sharing. How about if the data source is a SQL Server instance name? Can I use the same method in the video?

Fantastic work! Thanks for sharing :)

Hi nice explanation. And how about changing the SQL statement parameter based on cell value in Power Query?

Parabéns, excelente conteúdo, Já fiz a minha inscrição. Muito obrigado, sanou uma duvida monstro que tinha.

Thank you. A very useful trick.

Will this method work if I’m trying to pull a dynamic table name out of a static access database? The access database will not change but the table I’m pulling from will. All tables being dynamically pulled would have the same fields but different data?

I did not like it.... I L O V E D it.... so useful...... Thanks.... Do you have something similar for Access, linked table manager?

This is super useful, but I'm wondering how we would do this with SharePoint files? Is there a way to have a dynamic cell in Excel that calls to a SharePoint Folder path?

Thanks for this trick. Tricky part is to make on a file stored on a "One Drive" . Since the File Path formula gives you a web address and then through Power query you need to Login using Microsoft / Windows / Organizational Credentials. It worked well for the 1st time when I chose the path for the Source as below Source = Csv.Document(Web.Contents(FilePath & FileName)... But failed on subsequent refresh, and returns a 404 error not found... Any thoughts on how would you handle this?

thank you for the tutorial! I got one file to work but adding a second file and second set of references is failing :( so I am building a comparison PQ of current month data to prior month data and it's a ton of data :) I just need to be able to import 2 files by updating the file path references and then comparing these two files which I can do but creating a second import is troublesome

how can I do this with filepath and filename in a separate excel sheet? (rather than the current one)

What if you want this to point a table on a separate tab in the same workbook? Anything special that I should be aware of?

What if we have multiple file names that need to load all of them into multiple tabs

How to select multiple files

Im trying this for a Web query as i need to change the last letters of the URL: i saw (when dividing it in parts) that it does divide strings like this: ...page/catalogue/2022" & "/" & "January") i tried pasting my variable "month" like this: ... ...page/catalogue/2022" & "/" & Month) but it shows an error regarding a key not corresponding to a row in the table. Can someone help me find a solution? I'm new to excel so if im doing something really wrong im sorry 😅

Thanks can you please show the same for a SQL

I want to do it in my query, but the issue is that my table path is actually web path. So it is web query, is there a way to do the same for web query as well

How can I change the code if my source data is not in Table format?

How can you auto refresh after you change the File Path?

If the Power Query already has these as query Parameters is there a way of feeding the Parameters from a cell?

Hi Mark, what tweaks would I need if the source is a PDF?

Nice Sir

What if my data source is a web link? Will it work then?

Then how about we make the file name on drop down list? is that possible?

Thanks. This was a really useful video especially using named ranges. However, how do you solve the resulting Privacy error that occurs thereafter without setting the privacy settings to 'Ignore All'?

Thank you for a good video. Unfortunately I cannot make it work, when I want to reference to a folder of files (Only FilePath)? Can you help?

I get the following error: Expression.Error: The import Column1 matches no exports. Did you miss a module reference?

Hello, excellent video has been looking for this solution for a long time. Is this method possible now between an xlsx file and an odbc connection? Thank you

If you would want to add a browse button right beside the cell that contains the file path so that you won't need to manually copy and paste the file path in the cell, how you would do that?

This is great. What about if the file is located on SharePoint?

Hello Mark, Is it possible to update the power query name based on a cell value? I see that the step of renaming the power query is not recorded. So, is there a way or an option to update the power query name based on a cell value?

Does this work for web links?

I understand that it works only for the current workbook with the query.. What if am querying in a file which is connected to another database file? The database file is my source file. Is there any changes in M code when I change the database file into another location?

I need help on changing column name on daily to filter, when i first did my filter on my source column name is [xxx 25/10/2020] and filtered with "5" next day my column name is changed to [xxx 26/10/2020] when i refresh, it shows error like column is not found. Note: i dont want it to rename, i need source header as it is in output as well. I tried using table.columnnames(source){1} to filter based on column index instead of static column name as [xxx 25/10/2020] however, after refresh, it shows table is empty. There is no error but data is not getting populated. I need support to fix this.

Why did we have to separate the folder path and the filename? What if we tried to enter them together, at once?

I am getting this error: DataFormat.Error: The supplied file path must be a valid absolute path

What if your Query pulls data from a data base and you want to be able to change databases

If i need more than one excel file in folder ???

Trying to do this with a MDB file, sort of the same thing, but not getting it to work. any chance of a pointer to help?

Hello Mark, what will be the code if i have to define a variable based on a cell value in the current workbook for a hardcoded text value for ex: some filter value which is hard coded but I would want to define a variable with a cell reference for it in my excel sheet what will be the exact code to define it? Thanks for your reply in advance

What about getting the sheet name based on a cell value?

if my FilePath are numbers, how can i insert them?

Hey I'm having trouble with this, even though i've triple checked and everything matches, every time i try to refresh, i get this error: "We couldn't find an Excel table named 'FileName'." Has anyone else run into this issue?

I am getting the could not find the file.. The file Path and file name are correct.. How can I fix it?

Instead of adding that backslash at the end of the filepath in the cell (which if you're sharing this with less technical people, they will forget), why don't you code it into the source as FilePath & "\" & FileName