Power BI Desktop incremental table update from Power Query

Question

I'm gathering my data from a third-party REST API which has many thousands of records but updates each day with many more and so refreshing my data sources becomes a long task. So I was wondering if there was any way to do incremental loads and only scrape the last 30 days worth of data from the API and append that to a table in PowerBI Desktop.

I've looked into the append queries feature but this seems to only be able to join 2 queries and return a single table, which would eventually run into the same problems as the appending query will grow very large over time.

What I'm really looking for is a way to have a table in PowerBI which then gets updated on each query refresh. Has anyone seen if this is possible?

Any help would be great, thank you.

Answer 1

I would break this into two processes:

1. Excel Power Query extract from REST API for all historical data (e.g. filtered for "prior to last month", refreshed each month), delivered into an Excel table.
2. Power BI Queries that append the stored historical data from Excel with recent data via the REST API.

I would keep the starting Query against the REST API identical in both Excel and Power BI, so it can be easily maintained. Then you can Reference that into another Query that applies the specific date filters required.

Answer 2

I too had a similar environment using Excel to build the Power Querys and then importing the .xls file into PowerBI (PBI) desktop but that involves generating a new .pbix file and losing all development previously done. So now I get it when you say keep the Query the same in both applications (1: Excel & 2: PBI). Surely the MS PBI team could come up with some form of centralised source control for the queries (Query Data Catalog)in PBI.