Incremental refresh from API as zip file possible?

[Bokazoit]

I can retrieve the data as a zip file, unpack it and load it into a table in Power BI desktop, but how to add incremental refresh? CoPilot says that retrieving data as zip file removes query folding, so is there a way to do that by creating a flow or something?

[MarkLaf]

This is possible if your data source you are pulling zips from supports date filtering. See https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

Here is I think the most pertinent excerpt (my emphasis added):

---

Incremental refresh and real-time data works best for structured, relational data sources like SQL Database and Azure Synapse, but can also work for other data sources. In any case, your data source must support the following:

Date filtering: The data source must support some mechanism to filter data by date. [irrelevant info re: relational sources]

For other data sources, the RangeStart and RangeEnd parameters must be passed to the data source in some way that enables filtering. For file-based data sources where files and folders are organized by date, the RangeStart and RangeEnd parameters can be used to filter the files and folders to select which files to load. For web-based data sources, the RangeStart and RangeEnd parameters can be integrated into the HTTP request. 

---

This is sometimes easier said than done. For example, in SharePoint, you would do something like:

1. Query your document library using SharePoint REST with Web.Contents, using RangeStart and RangeEnd in $filter (converted to correct text format of yyyy-MM-ddTHH:mm:ssZ)
   
2. For each file in range, use SharePoint REST with Web.Contents again to get file/$value to actually download the binaries (zips) + unzip + parse binary contents + transform to homogeneous tabular shape
3. Expand your table column, ensuring you retain the date column you originally filtered as part of your query in #1
4. Re-apply the RangeStart and RangeEnd filters (which is redundant, but required for PBI to validate the incremental refresh - at least that was needed when I did something similar an admittedly pretty long while ago)

Note: ootb SP connectors do not support date filtering last I checked, which is why we would query REST endpoint with Web.Contents. Relevant MS doc for SP REST: https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-folders-and-files-with-rest

[anilelmastasi]

Hi @Bokazoit ,

Is it possible to load data into a database? You can use database as staging area and dbs support query folding.

[Bokazoit]

We are moving away from traditional Data warehouse. But I can use Dataflow Gen2 with a PPU licens?

[v-hjannapu]

Hi @Bokazoit,

Yes, Dataflow Gen2 can be used with a PPU license. Since you are moving away from a traditional data warehouse, this option should work well for you.

Incremental refresh will not work directly on ZIP files, but you can use Dataflow Gen2 to load and transform the data and store it in a Lakehouse table. Once the data is in the Lakehouse, you can build your model on top of it and use incremental refresh there.

So using Dataflow Gen2 as an intermediate step is the supported way to handle this scenario.

Hope this clarifies. Please let us know if you need any help setting this up.
Regards,
Community Support Team.

[v-hjannapu]

Hi @Bokazoit,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Community Support Team.

[v-hjannapu]

Hi @Bokazoit.,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[anilelmastasi]

Loading data into database is best practice but if you use Fabric, you can ingest data with Dataflow Gen2.

After that you can write results into Lakehouse table and build your model on top of the lakehouse.

If this solved your issue, please mark it as the accepted solution. ✅

[v-hjannapu]

Hi @Bokazoit,
Thank you  for reaching out to the Microsoft fabric community forum.

Incremental refresh works only when Power BI can push filters back to the source (query folding). When the data is downloaded as a ZIP file and then unzipped in Power Query, that folding is lost. Because of this, incremental refresh will not work in this scenario.

Even if you use Power Automate or any other flow to download and unzip the file, Power BI will still treat it as a file-based source, so incremental refresh is not supported there.

If incremental refresh is mandatory, the data needs to come from a source that supports folding, like a database or an API where you can filter data by date. Otherwise, a full refresh is the only option with ZIP files.

Kindly refer to the below documentation links for better understanding:
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Best Regards,
Community Support Team.