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.
As much as I like the views, I would advise creating a text file with the code in it and save it on your desktop. Then you can just copy/paste the code each time. Or at least, that’s what I do. 😁
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
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.
I previously would load parameter as table, then drill-down to a string. But even that did not work the other day, inexpicably. Happy to find this approach.
@@ExcelOffTheGrid This is great information. Can you show how to do this for a SQL statement? Eg. I want to just update 1 value in SQL without having to open the editor for each query. So my SQL statement might say where value=100 and I want to update them to say where value=200 using this method or something as simple.Thanks.
I try not to look back at old videos - they make me cringe. I’m sure I will look back at current videos and feel the same in a few months. 😁 It’s funny that my inability to know how to transition from an intro to the main content has ended up becoming a catch phrase. That’s video includes such a crucial technique. Maybe I should give it an update so that it reaches more people. 😁
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!
@@ExcelOffTheGrid thanks. So I've got an issue I'm using a table with 2 columns, one with a list of URL with the data source to these URL Is a csv. The issue I'm having is that if you change these URL, it's coming up with error message because it is keeping the headers of the first URL I did originally! What can I do to fix it? And I can't attach images here unfortunately
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.
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?
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.
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! 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.
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
I'm not sure to be honest. As I've not tried it with an ODBC. If the M code displays the the ODBC parameters separately then, Yes, I expect so. But you might encounter the Formula.Firewall error. If you do, you'll need to change the the Privacy Settings.
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
@@ExcelOffTheGrid Do you mean that you can't use the same name for the cell that is named FoderPath? What about for the begging of the code in the advanced editor where we must write FilePath=...Should it be changed?
@@tsvetelinalalova9036 The name of each step must be unique within the query. So if you've already used: FilePath=..... Then you need another name, such as FilePath2=.....
@@ExcelOffTheGrid Thank you. It works. I made three folder paths and named them "FolderPath", "FolderPath2", and "FolderPath3", and called the cells where it is placed the Folder paths in the same way. I made three queries from three folder paths and merged them in some stages in the data processing. Now I have the result I have wanted to achieve. I have only one idea - to set the formula in the cells where the folder path and when I put the file in a particular folder formula to generate the folder path, and there isn't any need to paste a link with the folder path.
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 ?
To fully understand M code takes quite a bit of learning. There is a great resource by Ben Gribaudo which gives a solid introduction to the language. bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let
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?
Yes that is possible. Here is how to connect to the file: th-cam.com/video/JDdjDWZdxLE/w-d-xo.html Then apply the same techniques as this video to get the dynamic path.
Sorry, I’ve not used Access for about 20 years. Might be worth checking out this video for some more technical considerations: th-cam.com/video/_MtuqO9Cj1E/w-d-xo.html It might help you out to apply it to other situations.
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!
In Excel go to: Data > Get Data (drop down) > From File > From Folder. Check out this post: exceloffthegrid.com/power-query-import-all-files-in-a-folder/ This will enable you import files in a folder. Once you've created the query, you can apply the same principles as the video to use a cell value to point to a folder (rather than files).
@@ExcelOffTheGrid Thank-you Mark...I've tried the steps you mentioned, however I get the error "Expression Error" We Cannot covert a value of type Record to type List. Details: Value = [Record] Type = [Type]. Note: I am Merging files from Folder and not Appending. Thanks Again!!
@@WJA5 I’m not sure why you are merging when combining files from a folder. I might be misunderstanding something. I don’t have a video for this technique, but you can check out this post: exceloffthegrid.com/power-query-import-all-files-in-a-folder/
@@ExcelOffTheGrid Thanks. I guess need to think about when to use meta parameters and when to use this named cell technique. When using with Power BI parameters are nice.
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?
Not being an SQL guy myself it’s difficult to answer. Presumably the SQL is a shown as a text string within the Advanced Editor, therefor the principles from the video would be similar. But in the M code you would replace the SQL statement rather than the file path.
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
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?
You need to start with the query already operating correctly (i.e. linked to whatever the original source is). The code for that query will have a hardcoded value. Use this technique to replace the hardcoded value with a cell link. Provided your original query works, then this technique should work. If you've got a different scenario then it sounds like you'll need to play with it, to see what it can do.
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
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 😅
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?
It's likely there is a typo, or something hasn't been setup in the right way. I suggest deleting the steps and trying again. It should work, I did it yesterday :-)
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.
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'?
That issue is because external queries with different privacy setting are both trying to land data in the same query. The best way to solve this is structurally. Have queries which import the raw data (load as connection only), then staging queries which manipulate the raw data query (load as connection only), then have final queries which provide the final format for loading the data to the Worksheet or Data Model. This also helps to manage queries in a structured way.
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?
To do that you should use a macro which is triggered by a button or picture. Check out the VBA code here: exceloffthegrid.com/selecting-file-using-filedialog/
@@ExcelOffTheGrid I already found the answer to my question but it turns out that for my case scenario it's actually better to have the file path relative to the file I'm working on! I already did that but both scenarios are good material for a video tutorial!
If you want all the files in a folder then use the Data > Get Data > From Files > From Folder method, then you only need a single parameter. Alternatively, you could: - Use the From Folder method - Create a separate Table with the file names listed - Apply a merge transformation which only keeps the files listed in the folder.
@@ExcelOffTheGrid would you be able to show an example of this? I am trying to do so but then the next applied step for finding the sheet name within the file is giving me "Expression.Error: The key didn't match any rows in the table".
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
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?
Hi Jim - Sorry, it's not something I have tried. It's been a while since I have used Access. Is all the information you need to connect to Access included in the text string in the PQ Advanced Editor?
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.
Is the database name is contained in the M-code as a string within the advanced editor? If so, then I think this should be possible using this technique.
Most helpful video ever. Not even ChatGPT was as clear as you. Thank you!
I find that Actual Intelligence always beats Artificial Intelligence 😂
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.
As much as I like the views, I would advise creating a text file with the code in it and save it on your desktop. Then you can just copy/paste the code each time.
Or at least, that’s what I do. 😁
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
Great work. That’s a nice addition 👍
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.
Yes!!! That’s exactly what I do 😁
So far the easiest and simplest method I found in youtube! Thank you!
I had used a harder version for years. Then I found this.
It just takes a little knowledge with M code. 😀
Thanks!!! I used this tutorial and had complete success!!!
Woop woop - good work. 👍
This is brilliant and helped me from copying and pasting from a bunch of different workbooks. Thank you!!
You’re welcome. Glad I could help.
I previously would load parameter as table, then drill-down to a string. But even that did not work the other day, inexpicably. Happy to find this approach.
Thank you, thank you, thank you! I have been looking for this all week!! So simple and useful - thanks a billion :D
No problem, glad I could help 😀
@@ExcelOffTheGrid This is great information. Can you show how to do this for a SQL statement? Eg. I want to just update 1 value in SQL without having to open the editor for each query. So my SQL statement might say where value=100 and I want to update them to say where value=200 using this method or something as simple.Thanks.
Works brilliantly! And the video was easy to follow. Thanks so much!
Thanks Chemelle, I’m glad you found it useful.
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 :)
I try not to look back at old videos - they make me cringe.
I’m sure I will look back at current videos and feel the same in a few months. 😁
It’s funny that my inability to know how to transition from an intro to the main content has ended up becoming a catch phrase.
That’s video includes such a crucial technique. Maybe I should give it an update so that it reaches more people. 😁
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!
Great news. Good work 👍
How? i still get errors
Just what I was looking for! Thank you!
You are welcome!
Sweet! It worked perfectly. A simple and easy to follow tutorial. Thank you!!
Thanks Peggy. Glad I could help 👍
I liked your video. That was exactly what I was looking for. Thanks!
Great, I'm glad it helped :-)
Then how about we make the file name on drop down list? is that possible?
Yes, you can. Just add a named range to the cell with the data validation drop down list.
Brilliant!! I used this method to update a stored procdure call based on the values inserted into a couple of spreadsheet cells. Many thanks :)
Nice work! I’m glad you could put it to good use.
Thank you. This is by far the easiest explanation for this.
Thanks - I've used a number of more complicated methods. But this seems to be the easiest and fastest.
Very good explanation. Very useful!
Thanks OZ :-)
This is great! I’m going to use this at work right away!
Great news... have fun!! 😀
I was trying to do this with Parameter queries and then I saw this, great technique, thank you!
It's all the same methods, but applied in a slightly different way. Hopefully this way is easy to replicate.
How can you auto refresh after you change the File Path?
I’ve got a video here that explains how to do it.
th-cam.com/video/1sD_HOX0RSk/w-d-xo.html
Does this work for web links?
Yes it can work with web links,
but you’ll probably need to deal with the Privacy settings and Formula.Firewall error.
@@ExcelOffTheGrid thanks. So I've got an issue I'm using a table with 2 columns, one with a list of URL with the data source to these URL Is a csv. The issue I'm having is that if you change these URL, it's coming up with error message because it is keeping the headers of the first URL I did originally! What can I do to fix it? And I can't attach images here unfortunately
Thank you, really useful !!! I applied it to Dynamic Web Query using dynamic URL based on cell values and it works too.
Well done - good work. The principles apply to so many areas.
What if we have multiple file names that need to load all of them into multiple tabs
Try this… th-cam.com/video/23nulGrMBAM/w-d-xo.htmlsi=bCC0Ck7AnjmSd5Vw
This worked out perfectly. Thanks!
Great news, I’m glad I could help.
Brilliant. Really helpful and easy to understand
Thanks Mark, I'm glad it was helpful 😀
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.
Thanks Jarek I appreciate that. I’m glad you’ve found them useful. 👍
You made my day, thanks and keep posting something new !! :)
Thank You. Glad I could help 😀
The best and simple way!
Thank you 😀
Very useful, thank you very much 🙏🙏🙏
You’re welcome. It’s a really useful technique.
Thanks a lot for most useful PQ trick 👍 Very well explained 🙏🙏🙏
Thank you. I hope you can put it to good use 😀
Great video thanks Mark
Glad you enjoyed it 👍
So cool! Worked great for me!
Great to hear! 👍
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?
Sorry, but I’ve not used it with an access database. Try it, and see.
Thank you for the awesome video! It's very very helpful
Glad it was helpful! 😊
This is simply a great method.
Thank You 😊
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?
Yes, this approach should work with any text string within the M code.
@@ExcelOffTheGrid Thanks so much. Yes, it works.
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.
Fantastic news - I’m glad you could put it to immediate use. 👍
i realize it is kinda off topic but do anyone know of a good site to stream newly released series online ?
@Dayton Rowen Flixportal
@Karter Morgan Thanks, signed up and it seems like a nice service :) I appreciate it !
@Dayton Rowen You are welcome xD
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!
That sounds like a really annoying scenario. I’m glad I could help out.
Thanks a lot 😊
No problem, glad I could help. 👍
Exactly what i need for my current project requiring flexibility for the file source. Thanks
You're welcome. I love a bit of flexibility.
Best Solution. No firewall formula error
I hate that error! Glad I could help.
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.
Great news, I'm glad it helped.
Thanks so much - very helpful.
You're welcome! Glad it helped 😊
Amazing! Thank you a lot!!!
You’re welcome, I’m glad you found it useful.👍
Awesome, very simple and usefull
Glad I could help :-)
Fantastic explanation !!! Tks
Thanks - I’m glad I could explain it well enough. 😀
Thank you, thank you and thank you!
No problem, glad I could help 😀
good stuff. Thanks for sharing.
Thanks for watching!
Great video!!! I use only to point to the folder path and that was exactly what I was looking for!
Excellent news :-) Glad you liked it.
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
I'm not sure to be honest. As I've not tried it with an ODBC. If the M code displays the the ODBC parameters separately then, Yes, I expect so. But you might encounter the Formula.Firewall error. If you do, you'll need to change the the Privacy Settings.
@@ExcelOffTheGrid Thank you very much, i´l trie
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
You can’t use the same names, but apart from that it, should be the same process.
@@ExcelOffTheGrid Do you mean that you can't use the same name for the cell that is named FoderPath? What about for the begging of the code in the advanced editor where we must write FilePath=...Should it be changed?
@@tsvetelinalalova9036 The name of each step must be unique within the query. So if you've already used:
FilePath=.....
Then you need another name, such as
FilePath2=.....
@@ExcelOffTheGrid Thank you. It works. I made three folder paths and named them "FolderPath", "FolderPath2", and "FolderPath3", and called the cells where it is placed the Folder paths in the same way. I made three queries from three folder paths and merged them in some stages in the data processing. Now I have the result I have wanted to achieve. I have only one idea - to set the formula in the cells where the folder path and when I put the file in a particular folder formula to generate the folder path, and there isn't any need to paste a link with the folder path.
That was awesome bro!
Appreciate it! :-)
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 ?
To fully understand M code takes quite a bit of learning. There is a great resource by Ben Gribaudo which gives a solid introduction to the language.
bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let
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?
Yes that is possible.
Here is how to connect to the file: th-cam.com/video/JDdjDWZdxLE/w-d-xo.html
Then apply the same techniques as this video to get the dynamic path.
Thank you so much Mark, you’re amazing!!!
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?
Sorry, I’ve not used Access for about 20 years.
Might be worth checking out this video for some more technical considerations:
th-cam.com/video/_MtuqO9Cj1E/w-d-xo.html
It might help you out to apply it to other situations.
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?
To use a table, everything is the same, except 1 thing. Instead of [Column1], you'll need to use the name of the column header from the table.
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!
In Excel go to:
Data > Get Data (drop down) > From File > From Folder.
Check out this post:
exceloffthegrid.com/power-query-import-all-files-in-a-folder/
This will enable you import files in a folder. Once you've created the query, you can apply the same principles as the video to use a cell value to point to a folder (rather than files).
@@ExcelOffTheGrid Thank-you Mark...I've tried the steps you mentioned, however I get the error "Expression Error" We Cannot covert a value of type Record to type List. Details: Value = [Record] Type = [Type]. Note: I am Merging files from Folder and not Appending. Thanks Again!!
@@WJA5 I’m not sure why you are merging when combining files from a folder. I might be misunderstanding something.
I don’t have a video for this technique, but you can check out this post:
exceloffthegrid.com/power-query-import-all-files-in-a-folder/
If the Power Query already has these as query Parameters is there a way of feeding the Parameters from a cell?
There is no way to do a direct switch. You will need to switch it over in the M code so that everything points to the new parameter.
@@ExcelOffTheGrid Thanks. I guess need to think about when to use meta parameters and when to use this named cell technique. When using with Power BI parameters are nice.
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?
Wyn Hopkins in the Access Analytics TH-cam channel has some videos covering that.
You should check that out.
Hi nice explanation. And how about changing the SQL statement parameter based on cell value in Power Query?
Not being an SQL guy myself it’s difficult to answer. Presumably the SQL is a shown as a text string within the Advanced Editor, therefor the principles from the video would be similar. But in the M code you would replace the SQL statement rather than the file path.
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
thaks for this - need to do this for a folder content PQ
Second this!
Fantastic work! Thanks for sharing :)
Thank you! Cheers! 😀
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.
Good news, I’m glad it worked 😀
It's awesome.. More videos needed In power query topic
Yes, there will be more videos in Power Query coming. You can also check out my blog for more Power Query content - exceloffthegrid.com
Works well. Thanks
Thanks Mario 👍
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?
You need to start with the query already operating correctly (i.e. linked to whatever the original source is).
The code for that query will have a hardcoded value. Use this technique to replace the hardcoded value with a cell link.
Provided your original query works, then this technique should work.
If you've got a different scenario then it sounds like you'll need to play with it, to see what it can do.
@@ExcelOffTheGrid I will try it and let you know.. 😊
That's far better than using parameters to pull data from the sheet
It's the same code as using the parameter from a sheet. But it is applied in a way that avoids the Formula.Firewall issue which can catch us out.
Hi Mark, what tweaks would I need if the source is a PDF?
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
It’s the same process, but instead of replacing the hard coded file path, you replace the hard coded filter value.
@@ExcelOffTheGrid Thanks a lot
It worked :-)
Thanks can you please show the same for a SQL
Sorry, SQL isn't my thing. 🤔
Thanks a lot!
No problem 👍
This is great. What about if the file is located on SharePoint?
This method works with any parameters, it’s not specific to File Paths.
So, just replace the text string containing the SharePoint file path.
@@ExcelOffTheGrid Thanks
@@iankr No problem 👍
Big thanks Mark.
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 😅
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?
It's likely there is a typo, or something hasn't been setup in the right way. I suggest deleting the steps and trying again. It should work, I did it yesterday :-)
how can I do this with filepath and filename in a separate excel sheet? (rather than the current one)
Thank you. A very useful trick.
Glad it was helpful! :-)
Nice & Easy
Thanks, I try to make things easy (it doesn't always happen though 🤪
Very Impressive!
Thank you 😊
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.
How can I change the code if my source data is not in Table format?
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'?
That issue is because external queries with different privacy setting are both trying to land data in the same query.
The best way to solve this is structurally. Have queries which import the raw data (load as connection only), then staging queries which manipulate the raw data query (load as connection only), then have final queries which provide the final format for loading the data to the Worksheet or Data Model.
This also helps to manage queries in a structured way.
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?
To do that you should use a macro which is triggered by a button or picture.
Check out the VBA code here:
exceloffthegrid.com/selecting-file-using-filedialog/
Actually, I think I’ll create a separate video in this, as it’s a question that I’m sure lots of people will have.
@@ExcelOffTheGrid I already found the answer to my question but it turns out that for my case scenario it's actually better to have the file path relative to the file I'm working on! I already did that but both scenarios are good material for a video tutorial!
@@mariansdraila My video maybe a few weeks away yet, so I’m glad you’ve got and answer already. Hopefully it will be useful to others 😀
Parabéns, excelente conteúdo, Já fiz a minha inscrição. Muito obrigado, sanou uma duvida monstro que tinha.
Ótima notícia, fico feliz em poder ajudar. (using Google Translate)
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?
The methodology is the same for a file or a folder. The M code will create a text string. That's the part of the code you would replace.
If i need more than one excel file in folder ???
If you want all the files in a folder then use the Data > Get Data > From Files > From Folder method, then you only need a single parameter.
Alternatively, you could:
- Use the From Folder method
- Create a separate Table with the file names listed
- Apply a merge transformation which only keeps the files listed in the folder.
What if my data source is a web link?
Will it work then?
Why did we have to separate the folder path and the filename? What if we tried to enter them together, at once?
Yes, you can do it all in a single cell. I just happened to set up the example with two separate elements 😀
@@ExcelOffTheGrid would you be able to show an example of this? I am trying to do so but then the next applied step for finding the sheet name within the file is giving me "Expression.Error: The key didn't match any rows in the table".
What about getting the sheet name based on a cell value?
The method is exactly the same. But rather than replacing the hardcoded file path in the M code, replace the hardcoded sheet name.
I've looking for how to do this. Thank you.
Glad to help :-)
Can you do something on XML data sources. I'm having so much difficulty trying to remove XML tags
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
Yes, that is an option. You could even use an if statement to add the "\" if it is missing.
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?
Not that I'm aware.
If you're using the data model in Excel, then changing names becomes a pain. So I'm not something you would want to do.
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?
Hi Jim - Sorry, it's not something I have tried. It's been a while since I have used Access. Is all the information you need to connect to Access included in the text string in the PQ Advanced Editor?
@@ExcelOffTheGrid thanks. I’ve sorta got it working now
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.
I get the following error:
Expression.Error: The import Column1 matches no exports. Did you miss a module reference?
What if your Query pulls data from a data base and you want to be able to change databases
Is the database name is contained in the M-code as a string within the advanced editor? If so, then I think this should be possible using this technique.