• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Save Query and apply to next day file import|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Save Query and apply to next day file import|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QuerySave Query and apply to next day fi…
sp_PrintTopic sp_TopicIcon
Save Query and apply to next day file import
Avatar
Mark Castro

Active Member
Members

Xtreme Pivot Tables

Power BI
Level 0
Forum Posts: 5
Member Since:
October 22, 2015
sp_UserOfflineSmall Offline
1
November 3, 2019 - 8:10 am
sp_Permalink sp_Print

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
November 3, 2019 - 8:45 am
sp_Permalink sp_Print

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

Avatar
Mark Castro

Active Member
Members

Xtreme Pivot Tables

Power BI
Level 0
Forum Posts: 5
Member Since:
October 22, 2015
sp_UserOfflineSmall Offline
3
November 3, 2019 - 9:02 am
sp_Permalink sp_Print

I will give this a try Mynda and report back. Thank you for the quick response. Fan and follower of your work/teachings.

Avatar
Mark Castro

Active Member
Members

Xtreme Pivot Tables

Power BI
Level 0
Forum Posts: 5
Member Since:
October 22, 2015
sp_UserOfflineSmall Offline
4
November 3, 2019 - 10:15 pm
sp_Permalink sp_Print

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 pq1-1.JPGImage Enlarger

 

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.

sp_PlupAttachments Attachments
  • sp_PlupImage pq1-1.JPG (11 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
5
November 4, 2019 - 7:57 am
sp_Permalink sp_Print

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

Avatar
Mark Castro

Active Member
Members

Xtreme Pivot Tables

Power BI
Level 0
Forum Posts: 5
Member Since:
October 22, 2015
sp_UserOfflineSmall Offline
6
November 4, 2019 - 11:41 am
sp_Permalink sp_Print

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. PQ2.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage PQ2.png (94 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
November 4, 2019 - 12:10 pm
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Richard Benson-King
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27210

 

Member Stats:
Guest Posters: 49
Members: 31878
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.