Log In
Register
Home
Active Member
Xtreme Pivot Tables
Power BI
October 22, 2015
Offline
Hello Experts. I have a query that I would like to save for the next day file import and apply my saved query to it. How would I achieve this? I know I can create a folder on my desktop and save excel files then open PQ and click refresh but this is not exactly what i want to achieve. I want to open a new file then import in PQ then run my saved query. Is this possible?
Thanks in advance and I look forward to your feedback.
July 16, 2010
Offline
Hi Mark,
I'm having trouble following the scenario. If you want to re-use a query then you can export them to an ODC file; right-click the query in the queries pane > export connection file > save.
To reuse the query go to the Data tab > Existing Connections > browse to the query.
If that's not what you mean then perhaps you can rephrase your question with reference to workbook A and workbook B so I can follow what you're trying to do.
Mynda
Active Member
Xtreme Pivot Tables
Power BI
October 22, 2015
Offline
I will give this a try Mynda and report back. Thank you for the quick response. Fan and follower of your work/teachings.
Active Member
Xtreme Pivot Tables
Power BI
October 22, 2015
Offline
Hi Mynda
The Existing Connection option is greyed out. What I did was open XLS file, import to PQ 2016 and created my Query/Save and Load. I am not able to follow your easy steps since the Existing connection area is greyed out. After I closed and loaded my power query i saved my file to desktop. What am i overlooking?
On another note: I want to be able to save new files in a folder i created on my desktop where i can add new files every day and refresh my query and have the new data imported into my initial report I completed. I establish connection to a folder by going to New Query/Folder Path. Added the Excel.Workbook([Content]). I then click on Edit which loads a dialog box where I can see my file. I remove all other columns keeping Binary and Name columns. I click on Add Custom Column. New Name Column is Import and I add the =Excel.Workbook(Content]), click OK and I now see the added Import column. I click on the arrows to Expand and select only "Data". I click on the arrows again to expand. I keep all column listed. I can now see my imported report. I complete my query steps and click Close and Load. I save my file. No issues so far. Not until I go to add the second day2 file using the same prefix (file1 11-1-19, file2 11-2-19). Only the dates change. I open up the PQ file and click refresh. I see my new data added but also see column headers from my new file. Am i suppose to remove my headers from my first file used to create and run my query? I promoted the headers in my first file. I'm guessing I should remove the headers initially and just rename the columns named Import? Image attached 
and setting up Excel.WorkBook([Content]) etc... File in folder look like this: (file1 ENGR 11-1-19, file2 is ENGR 11-2-19. This will repeat ever day)
Jumping to the last part of adding new files to my folder for this query because this is where i get the error. After I open my power query file from file1 i click refresh and get a error message that says Cannot find ENG 11-2-19 file.
July 16, 2010
Offline
Hi Mark,
In regards to your original question, it sounds like you missed the first step of actually creating the ODC file: right-click the query in the queries pane > export connection file > save.
In regards to your second question, using Get Files > From Folder should bring up a dialog box that has a button at the bottom to 'Combine and Edit'. If you don't have this button then you need to update Excel so you can get the new Power Query functionality (even with perpetual licences you can upgrade Power Query). The steps are explained here: Power Query get files from a folder. Note: the column labels in each file must be the same.
Mynda
Active Member
Xtreme Pivot Tables
Power BI
October 22, 2015
Offline
Hi Mynda Thank you again for your response. I know this should be easy but this is what I see when right clicking in the query pane.. No export connection file. 
July 16, 2010
Offline
Looks like a limitation of the version of Excel that you have. You can try updating Excel to see if you get the new Power Query ODC export connection feature, or simply create a file with the query that you want to reuse. Then make a copy of the file, this will include the query.
If the file containing your query already has a load of other stuff in it that you don't want to copy, then you can just copy the query to a new workbook: CTRL+C the query in the Workbook Queries Pane > go to your new Excel file and create a blank query > paste the query into the Advanced Editor.
Mynda
1 Guest(s)
