Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Schedule Daily Incremental Data Loads In Power Pivot

  1. #1
    New Member
    Join Date
    May 2011
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Schedule Daily Incremental Data Loads In Power Pivot


    Schedule Daily Incremental Data Loads In Power Pivot
    I am trying to figure out how I can automatically schedule daily excel data files to be appended to a power pivot dataset. The process I am envisioning is as follows:


    • A large daily sales detail file exists in an excel table that is utilized in power pivot
    • I receive a system generated daily sales detail file (with the exact same columns as the large master dataset) that is saved to a specified folder
    • Power Pivot (Power Query??) automatically goes out to the daily sales file, opens it, and appends it to the large master table


    The goal is to have this incremental data load happen automatically every day. I've downloaded Power Update, but it appears that this is for complete data refreshes. I am sure I can use some VBA to accomplish this, but would prefer to see if a better/easier solution exists before I go down that path.

  2. #2
    Board Regular RudiS's Avatar
    Join Date
    May 2015
    Location
    Cape Town, RSA
    Posts
    349
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Schedule Daily Incremental Data Loads In Power Pivot

    I would recommend to use Power Query to "maintain" your Data Model (the content to which Power Pivot is connected to).
    With Power Query you can use "Import from Folder" as the source; meaning that the moment you add a new Sales Detail to the folder, it will be integrated into the Data Model. Once this is set up, you can modify the Connections Properties (DATA ribbon > Connections > Properties) (of this workbook containing the Data Model and Power Pivot) and have it update automatically when the workbook is opened.

  3. #3
    New Member
    Join Date
    May 2011
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Schedule Daily Incremental Data Loads In Power Pivot

    Thanks for the reply. I came across Get data from Folder in Power Query | Excel Unplugged, which has a very helpful step by step process outlined for importing multiple files. Unfortunately, when I use this technique on multiple excel files I get the following message:

    DataFormat.Error: The input could not be recognized as a valid Excel document. Details: Binary

    I have no idea why this message is appearing as this seems like a very basic import, but further research pointed me to https://cwebbbi.wordpress.com/2014/1...ythe-easy-way/ and Combining Data From Multiple Excel Workbooks With Power Query. It looks like this might be able to help me get around the error message. I’ll try it out and let you know how it goes.

  4. #4
    New Member
    Join Date
    May 2011
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Schedule Daily Incremental Data Loads In Power Pivot

    Accidental duplicate post
    Last edited by Hopeless Cub Fan; May 9th, 2015 at 11:00 AM. Reason: Accidental duplicate post

  5. #5
    Board Regular RudiS's Avatar
    Join Date
    May 2015
    Location
    Cape Town, RSA
    Posts
    349
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Schedule Daily Incremental Data Loads In Power Pivot

    Yes, Power Query expects the files in a folder to be of the same type. If it is not, you can access the Navigation step in the Applied Steps window and use a filter to filter for the type of file you want to process. Once the filter is applied and you have only one type of file format, it will happily continue to process all files of that type in the folder.

    What file type are you working with? XLSX or CSV or TXT?

  6. #6
    Board Regular
    Join Date
    Dec 2012
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Schedule Daily Incremental Data Loads In Power Pivot

    Hey!

    I think this might be the thing you need Append or Combine Data in Excel - Power Query Training
    You can combine anything that you want. Excel, txt and / or csv ....at the same time! Check it out

  7. #7
    New Member
    Join Date
    May 2011
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Schedule Daily Incremental Data Loads In Power Pivot

    RudiS,

    Files are .xlsx. I'll try the mentioned menus.

    Miguel,

    I'll give the process you outline a try. Thanks!

  8. #8
    Board Regular RudiS's Avatar
    Join Date
    May 2015
    Location
    Cape Town, RSA
    Posts
    349
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Schedule Daily Incremental Data Loads In Power Pivot

    Quote Originally Posted by miguel.escobar View Post
    Hey!

    I think this might be the thing you need Append or Combine Data in Excel - Power Query Training
    You can combine anything that you want. Excel, txt and / or csv ....at the same time! Check it out
    Hi Miguel...

    Amazing website (and training) that you and Ken have put together. I was dumbstruck by the website design and the professionalism of the content. you guys are amazing! Keep up the excellent work!

    Cheers!

  9. #9
    Board Regular
    Join Date
    Dec 2012
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Schedule Daily Incremental Data Loads In Power Pivot

    Rudis,

    Thank you for your kind words! I'll pass them to Ken also. We're extremely happy to have that site and promote Power Query to the masses as much as we can.

  10. #10
    New Member
    Join Date
    May 2011
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Schedule Daily Incremental Data Loads In Power Pivot

    Miguel,

    I followed the steps you outlined at the link above and it worked very well. Nicely written and easy to follow article! I'm going to order the M book also to learn more about Power Query. Seems like it is very powerful and can help me a ton.

    Is there a way in power query to automate this refresh daily via scheduling without my involvement every day? I am looking to avoid using SharePoint or PowerBI cloud initially as this is a proof of concept, and I would prefer to avoid that level of complexity at this stage.

    RuidS,

    Thanks for your help also! Your suggestions also worked.

Some videos you may like

User Tag List

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
  •