June 10, 2019 at 3:55 pm #17980
Because Power Query can’t import data from Power Pivot, there’s no way to append new data to existing data in Power Pivot.
If so, the only way to incrementally update Power Pivot in Excel is to maintain a collection of workbooks in a folder, where each workbook uses Power Query to update an Excel *Table*. And then we use Power Query to refresh Power Pivot by appending the data from all of those workbooks.
Or is there a more elegant way to incrementally update Power Pivot in Excel?June 10, 2019 at 4:40 pm #17981
I just thought of another way…
Under macro control, we probably could control a PivotTable that reads the Power Pivot data one chunk at a time, then appends the data to Power Query. Finally, the new data could be appended to that PQ table, and then the incrementally updated query could write everything back to Power Pivot.
By the way, there could be several reasons for needing to do this…
…The data in PP no longer is available elsewhere. So we need to append new data to the existing data.
…The data comes from the web and might require *hours* to do a full–and unnecessary–refresh.
…The data comes from the web and we want to minimize how frequently we access data from a certain site.
Any thoughts?June 10, 2019 at 5:42 pm #17983
- Started: 7
- Replies: 2556
- Total: 2563
A couple of ways to handle this scenario:
1. Create a folder dedicated to the files that make up the chunks of your reporting dataset, and then import into the Power Pivot window using Power Query’s import from folder option.
2. Store the chunks of data into a data store like SQL Server or Access, and then import into the Power Pivot window using a connection to the data store.June 10, 2019 at 6:11 pm #17984
Your suggestion #1 is exactly what I had in mind with my first message. The VBA solution probably would be more flexible, but more work to build.
Now that I know that I’m not missing something obvious, I’ll set up a bunch of workbooks. Because the source refreshes each dataset at different times, I’ll set it up so that each workbook contains data with approximately the same refresh date.
You must be logged in to reply to this topic.