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

 

[piannetta]

Wow, I didn't think this was going to be such a challenging problem, I'm guessing by the lack of replies that what I'm asking isn't possible using Power Query in Excel...

 

[sandy666]

any chance for a sample of two sources ("yesterday" and "today") with few representative rows and expected result?

 

[piannetta]

Thanks Sandy, I've not tried uploading samples but using the Forum Add-in tools, here goes:

Current Dataset:
Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#70AD47]CallStartDt_d[/td][td=bgcolor:#70AD47]KoganOff[/td][td=bgcolor:#70AD47]HCFOff[/td][td=bgcolor:#70AD47]AFSOff[/td][td=bgcolor:#70AD47]PerfectPartnersOff[/td][td=bgcolor:#70AD47]PetSecureOff[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#E2EFDA]

1/07/2015 0:00​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

41​

[/td][td=bgcolor:#E2EFDA]

23​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

25​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

2/07/2015 0:00​

[/td][td]

0​

[/td][td]

56​

[/td][td]

10​

[/td][td]

0​

[/td][td]

58​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#E2EFDA]

3/07/2015 0:00​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

32​

[/td][td=bgcolor:#E2EFDA]

17​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

34​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

6/07/2015 0:00​

[/td][td]

0​

[/td][td]

53​

[/td][td]

31​

[/td][td]

0​

[/td][td]

44​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td=bgcolor:#E2EFDA]

7/07/2015 0:00​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

69​

[/td][td=bgcolor:#E2EFDA]

21​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

38​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: QRYCallsOfferedByBrand[/td][/tr][/table]

New Dataset:
Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#70AD47]CallStartDt_d[/td][td=bgcolor:#70AD47]KoganOff[/td][td=bgcolor:#70AD47]HCFOff[/td][td=bgcolor:#70AD47]AFSOff[/td][td=bgcolor:#70AD47]PerfectPartnersOff[/td][td=bgcolor:#70AD47]PetSecureOff[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td=bgcolor:#E2EFDA]

8/07/2015 0:00​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

51​

[/td][td=bgcolor:#E2EFDA]

16​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

38​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: QRYCallsOfferedByBrand[/td][/tr][/table]

Ideal outcome is the new dataset is added as a row at the end of the current dataset.

I hope that gives you an idea of what I'm trying to do

Cheers,
Pete

PS: I'm on Windows using Excel 2016 via Office360

 

[sandy666]

with your example simply use Append New to Current

 

[piannetta]

Thanks Sandy, I'll look into that today. Cheers.

 

[sandy666]

You are welcome

 

[piannetta]

So I've had a play around, I have my existing query (that I won't refresh) and my new query that just queries data for yesterday, and I've appended the new query to the existing query. So I'm assuming that I no longer need to refresh my existing query and only refresh the new query each day. But I've got a few questions if you don't mind:
1) If I don't refresh my existing query, it doesn't seem to pick up the data produced by the new query, so how do I get around my original problem, which is not running the original query which grabs 2 years of history and takes ages to complete?
2) I've setup the new query (which is appended to the current query) to look at "yesterday", but I've found that when I refresh it and the existing query, it's only giving me yesterday. So when I get to tomorrow, yesterday (which is the day before yesterday) will disappear and I only see yesterday

What I need to happen is the result from yesterday's refresh of the new query to remain and have the result of thew new query from today be added, and so on moving forward.

Either I've misunderstood how to setup an Append query or I'm not using the right mechanism for what I want to do. Any tips you can offer please?

 

[sandy666]

all your history data is in one file or each day (month, year, whatever) are in a single files?

could you show structure where and what is?

(I prefer "military" description )

 

[piannetta]

No worries.

I'm querying a single SQL Server DB table, it contains all the data from the last 3-4 years. At present, I have a single query that grabs all that data and pops it into an Excel spreadsheet. Every day I refresh that query so I can get the latest data (usually for yesterday) and the table in Excel is updated. This refresh can take up to 5-6 minutes, and I have approximately 25 queries in this Excel file all doing the same thing (on different SQL tables). So each morning, a refresh of all of these queries just to get yesterday's data added to each table takes roughly an hour.

Ideally, I'd love the Excel table to be built incrementally each day by only querying yesterday's data. I expect that the daily refresh would take no more than a few minutes as it's just grabbing a single day's data.

I hope that paints a picture of the structure and what I'm doing with it. The Append approach seemed on face value to be what I wanted but upon execution, I can't seem to figure out how to preserve the outputs of previous queries while still adding incrementally each day.

Overnight I was thinking if there's not another way to do this, then I could write some VBA that takes the output of the previous day's query and copies and pastes that over to the main table with all the history. But I'm open to all ideas so if after reading the above you think there's a more efficient way to do this, happy to hear your thoughts. And thanks again for taking the time to help me, very much appreciated.