Tips & Tricks
Are you looking for some of the best-known Tips & Tricks or have some to share. This is the place to... View more
Organizer:
- Organized by
-
-
Reusing a Query
-
Reusing a Query
This one is for @DavidCutler who keeps rewriting his Power Queries rather than reusing them.
This is the O365 method using TEXTBEFORE, if you’re still not on this version, you’ll have to use the old LEFT, RIGHT functions.Somewhere in your workbook, type this:
=CELL(“filename”,<cell reference>).
the ‘filename’ part will come up in a list as soon as you open the parentheses on CELL.
For a cell reference, I usually use A1 on whatever sheet in your workbook you’re putting this formula on. Make sure you lock this cell using F4.
You should get something like this (I used mine, but you get the idea)
….townemillworkltd-my.sharepoint.com/personal/doug_mclean_townemillwork_ca/Documents/Desktop/[Test Workbook.xlsx]Sheet1Next what you want to do is name the cell. In the Cell reference box(the box right above A1 & B1), give it a name, something like FilePath.
You can also shorten the cell formula using functions like TEXTBEFORE. Let’s take out the file name by doing this: =TEXTBEFORE(CELL(“filename”,$A$2),”[“). The [ functions as a delimiter in this case. You can use any delimiter you like, you can also add anything you like after the closing parentheses by adding an & and “text in quotes”. Once you get this part sorted out, most of your work is done.
Next you need to load this cell as a Connection Only query in PQ. Rt. Click in the cell > Get Data from Table/ Range. Now edit your query to only keep the Source step. Change the data type on the column to text. Then on the row, Rt. Click > Drill Down. Rename your Query to ttlFilePath. Close and Load> Close and Load to> Only create a Connection.
Now all you have to do is substitute “ttlFilePath” where ever you have a file path in your source step.
The first time you do this, go through the steps. Get Data from text/CSV file. Where its showing the file path, highlight that and overwrite with ttlFilePath.= Csv.Document(Web.Contents(ttlFilePath & “/Markup Summary.csv”),[Delimiter=”,”, Columns=51, Encoding=65001, QuoteStyle=QuoteStyle.None])
That’s it. Now your Query is fully scalable. The filename will change based on whatever folder its stored in. Just save a copy of your worksheet in a template folder somewhere.
-
2 Replies
-
Points: 26,859Rank: UC2 Brainery Advanced Brown Belt
Thank you @Doug McLean !
One of my goals for 2025 is to figure this out! Thank you for getting me setup to succeed on this!😀
-
Points: 15,282Rank: UC2 Brainery Advanced Blue Belt
you can also copy/ paste the code into the advanced editor if you need to.
Home> Advanced Editor.
-
Log in to reply.