• 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

Incremental Append in Get And Transform - controlling when the queries are run|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Incremental Append in Get And Transform - controlling when the queries are run|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 QueryIncremental Append in Get And Trans…
sp_PrintTopic sp_TopicIcon
Incremental Append in Get And Transform - controlling when the queries are run
Avatar
Sue Bristow
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 3, 2020
sp_UserOfflineSmall Offline
1
April 9, 2020 - 9:24 pm
sp_Permalink sp_Print

After hours of searching both you tube and via google, I have found the solution to incremental append without going to Power BI.  Not straightforward but it appears to work.

My question now relates to how to keep that data refreshed.

I am intending to have the workbook with the complete table as a standalone file that I will use as a basis for other reports.  This means that I will not actually be opening the file when I reference it in the other reports. 

I want the queries that create the data to refresh every night so that once the overnight report has run, the query refreshes and the data is up to date.  I have looked at the Connections dialogue box and in Properties you can refresh every 60 minutes.  I can't find anywhere where you can set a time to refresh.  Will this refresh the data without opening the file please?

Many thanks

Sue

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4436
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
April 9, 2020 - 9:39 pm
sp_Permalink sp_Print

Hi Sue,

Excel doesn't have an option to refresh the query at a specific time, only time intervals. Nor can a file execute a refresh when it's closed. However, you can set it to automatically refresh upon opening.

Mynda

P.S. I'd be interested to know your solution to incremental refresh without using Power BI!

Avatar
Sue Bristow
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 3, 2020
sp_UserOfflineSmall Offline
3
April 10, 2020 - 7:33 pm
sp_Permalink sp_Print

Hi Mynda

Thanks - I'll have to think about how I'm going to update this information then!

I found the information in a few different places but found it really difficult to follow.  Eventually it seems to be working but not quite how either of these solutions seem to show.  I think I have a different version of office which is what I suspect the differences are about.

https://blog.jamesbayley.com/2.....ory-table/

disable_polymer=true

The You Tube video is what I mostly followed.  Running that on one screen, pausing and replaying while doing it on my data on another screen.  I normally put my data into a data model rather loading to a worksheet but I can't see how to do that.  I'll live with it!

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4436
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
April 10, 2020 - 8:40 pm
sp_Permalink sp_Print

Hi Sue,

Thanks for sharing. It works well for data that's in your worksheet, but it won't load the data to the data model because it requires a reference back to itself and Power Pivot cannot be a data source.

This application has limited real use because typically the reason for wanting incremental refresh is because you're working with a lot of data and you wouldn't want to be storing large amounts of data in the worksheet. Large datasets really need to be in Power Pivot/Data Model.

Mynda

Avatar
Sue Bristow
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 3, 2020
sp_UserOfflineSmall Offline
5
April 14, 2020 - 6:22 pm
sp_Permalink sp_Print

Ah - thank you for that.  My understanding of how things really work is fairly limited but I'm good at googling 🙂

For me it is fine, not huge amounts of data.  One school has about 500 students on roll with a fairly high turnover of students so probably 700 max on the "ever on roll during an academic year" table and the other school about double both of those numbers.  Perfectly workable at this scale thankfully.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: John Langham-Service, Deirdre Leigh, Suzanne Halfacre, Riny van Eekelen, John Kobiela, Manjula Mangalmurti
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:
Lawrence Miu
Jo Saies
Paul Pritchard
Monique Roussouw
Keith Aul
Richard Dunks
Manjula Mangalmurti
Thomas Quidort
Jamie Preece
Bob Smith
Forum Stats:
Groups: 3
Forums: 24
Topics: 6192
Posts: 27150

 

Member Stats:
Guest Posters: 49
Members: 31848
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.