Schedule Daily Incremental Data Loads In Power Pivot

[Hopeless Cub Fan]

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.

 

[RudiS]

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.

 

[Hopeless Cub Fan]

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/...excel-workbooks-with-power-querythe-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.

 

[Hopeless Cub Fan]

Accidental duplicate post

 

[RudiS]

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?

 

[miguel.escobar]

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

 

[Hopeless Cub Fan]

RudiS,

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

Miguel,

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

 

[RudiS]

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!

 

[miguel.escobar]

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.

 

[Hopeless Cub Fan]

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.