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.
-
5 Replies
-
Points: 27,671Rank: UC2 Brainery Advanced Brown Belt I
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,673Rank: UC2 Brainery Advanced Blue Belt
you can also copy/ paste the code into the advanced editor if you need to.
Home> Advanced Editor.-
Points: 27,671Rank: UC2 Brainery Advanced Brown Belt I
I think that I figured out my problem with being able to re-use my Power Query @Doug McLean !
The problem I found wasn’t in the “source” information (the information you provided previously on how to change the source location helped me with this). The problem was that I was deleting the output data table from my file! So basically I was successfully telling PQ where to find the data and what to do with it, but I had taken away the place that the query was setup to place the processed data. I had assumed that PQ would generate a new tab in the workbook when I selected “Close & Load” – since that is what it does when you first setup the Query.
So I now have a template file that includes the Power Query (with the output sheet) ready to go! Very excited to use this in the New Year!
Now, to work through the information you provided above to automate the process further… 😎
-
Points: 15,673Rank: UC2 Brainery Advanced Blue Belt
that’ll do it.
When I set up a template file, I just delete all the table rows except the top one, then clear the data in that top row. Just leave yourself a header row and one blank row.
At that point to you can record a macro to refresh and place it on a button.
What yours was doing was creating a Connection Only Query,
You could have fixed this by opening the Queries and Connections pane (Data> Queries and Connections) then Rt. Click> Load to.keep learning
-
-
-
-
Points: 27,671Rank: UC2 Brainery Advanced Brown Belt I
Thank you for the tips @Doug McLean ! I figured it would be something simple – it usually is. Classic user error. 🤣
Log in to reply.