Adding only the latest data to existing table
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Adding only the latest data to existing table

  1. #1
    New Member
    Join Date
    Aug 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Adding only the latest data to existing table

    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

  2. #2
    New Member
    Join Date
    Aug 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding only the latest data to existing table

    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...

  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,670
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Adding only the latest data to existing table

    any chance for a sample of two sources ("yesterday" and "today") with few representative rows and expected result?
    Last edited by sandy666; Mar 5th, 2019 at 10:09 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  4. #4
    New Member
    Join Date
    Aug 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding only the latest data to existing table

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

    Current Dataset:
    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    F
    1
    CallStartDt_d KoganOff HCFOff AFSOff PerfectPartnersOff PetSecureOff
    2
    1/07/2015 0:00
    0
    41
    23
    0
    25
    3
    2/07/2015 0:00
    0
    56
    10
    0
    58
    4
    3/07/2015 0:00
    0
    32
    17
    0
    34
    5
    6/07/2015 0:00
    0
    53
    31
    0
    44
    6
    7/07/2015 0:00
    0
    69
    21
    1
    38
    Sheet: QRYCallsOfferedByBrand

    New Dataset:
    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    F
    1
    CallStartDt_d KoganOff HCFOff AFSOff PerfectPartnersOff PetSecureOff
    7
    8/07/2015 0:00
    0
    51
    16
    1
    38
    Sheet: QRYCallsOfferedByBrand

    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
    Last edited by piannetta; Mar 12th, 2019 at 05:28 PM.

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,670
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Adding only the latest data to existing table

    with your example simply use Append New to Current
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #6
    New Member
    Join Date
    Aug 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding only the latest data to existing table

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

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,670
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Adding only the latest data to existing table

    You are welcome
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    New Member
    Join Date
    Aug 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding only the latest data to existing table

    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?

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,670
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Adding only the latest data to existing table

    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 )
    Last edited by sandy666; Mar 14th, 2019 at 02:54 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #10
    New Member
    Join Date
    Aug 2002
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding only the latest data to existing table

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •