Adding only the latest data to existing table

[piannetta]

Hi, I am relatively new to Power Query in Excel, I am really enjoying it though and can't believe I didn't pick up on years ago. Anyway, that aside, I've built a number of spreadsheets with a number of queries within each that automate a lot of my reporting and tracking activities.

The queries are built to get all data for date greater than a specified starting date within the query. What I'm finding though is that as each day passes, it's taking longer and longer to run these queries. Ideally, what I'd like to be able to do is build them in such a way as it just queries the data for yesterday (for instance) and just add that to the existing table that contains all the previous historical data. In doing this, it should take a fraction of the time to refresh each morning.

I'm just not sure how best to do this. I did look up "Append" queries but on the face of it, that doesn't seem to be what I'm after.

Any guidance or pointing in the right direction would be greatly appreciated.

Cheers,
Pete

 

[sandy666]

maybe this way...
create new query

=DateTime.LocalNow()+#datetime(0,0,-1,0,0,0)

it will be yesterday datetime
merge this to your sql table by LeftOuter
it will give you data from yesterday
then load this data into the sheet
now..... vba is a good solution for adding tables one by one I think. It will copy data table from yesterday and add to the end of previous copied data

tomorrow after refresh you will get today's data because of query above

don't forget this is a datetime so maybe change format to date in both queries, sql and just created query

define how often refresh you want then define copy time in vba

this is theory of course because I can't test it but I think this is worth to try

 

[sandy666]

if your excel file is open all the time - define how often you want to referesh,
if not - check box referesh on open file
depending on that - define copytime in your vba

 

[macfuller]

tl;dr There's no way to only take incremental updates into a PQ table. It's all or nothing. It's something all of us wish we could overcome.

Even if you take a "base" table snapshot of cumulative data the last day of each month, generate a delta of each day's file for the next month, you'll still have to append this month's "delta" with the cumulative "base" to get a single table. And because your "base" file changes each time you load it, unless you save it out again it will all be lost the next time you run the query. It might save you time if your "base" file is large - you can experiment. This assumes that your "base" data never changes once you've loaded it. For the data I retrieve from our ERP with SQL, historical records can be modified, so I have to take the whole enchilada each day to ensure I'm accurate.

Keep at it though.

I consider you lucky - getting 2 1/2 years of 6 data tables each with 3+ million rows (not to mention the lookup tables that change every day) takes me almost 4 hours now. But a lot of that is the 200+ measures and umpteen calculated columns I've had to add. I live with it - the result is so incredibly useful that I run it on Mondays and use it the rest of the week.

If I want to make changes to the measures or columns I have to filter the queries to only have 1 year max of data, make the changes, then re-run at full capacity. That probably fills everyone who reads this with horror at my poorly-constructed queries and DAX, but short of hiring the Italians to go through every line of code I haven't been able to optimize. Since the data takes so long to load, and for a lot of what I do I want the full data load, it's been easier to have a single file that's a smorgasbord of everything rather than having separate files for specific tasks - I'd probably still have hours-long data loads even with a focused set of measures. Maybe I'll test that someday.

I bring this up because you will face the same choices with your report as the number of ways you can squeeze insights from the data grows. I would be curious as to how you, and others viewing this, make design decisions. I use the data for analysis rather than dashboards so the measures rather than the visuals are where I get value from PowerX tools.

 

[piannetta]

Thanks Mac, glad I'm not unique in dealing with this issue. I'm going to chip away at it, I'll take Sandy's ideas and see if they will help me. My spreadsheet is all about insights and analytics and the data is incredibly useful.