API Incremental Refresh

[TSchmidt]

Hello,

I am currently trying to configure Incremental Refresh on a query connecting to a web api. I have created RangeStart and RangeEnd parameters filtering the query date/time column. My configuration looks like this:

Everything is loaded correctly into the premium capacity workspace where partitions for the dataset is created when running the first refresh. My issue is that historical data is not loaded into the model:

If i try to run a refresh on a single historical partition it returns 0 rows from SSMS. How do i get the model to load historical data as well?

Best regards

Troels

[v-tangjie-msft · Accepted solution]

Hi  @TSchmidt ,

As you can see in your picture, there is a warning. If you see this warning and want to verify the necessary query folding is occurring, use the Power Query Diagnostics feature or trace queries by using a tool supported by the data source, like SQL Profiler. If query folding is not occurring, verify the filter logic is included in the query being passed to the data source. If not, it's likely the query includes a transformation that prevents folding. This results in incremental refresh exceptions, please refer to the following document to check the steps:

Power BI Incremental Refresh with Web API Data Source | Data and Analytics with Dustin Ryan (sqldust...

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[TSchmidt]

Hello @v-tangjie-msft ,

Thank you for the references. Since my dataset automatically created partitions according to my configuration, i thought queryfolding was verified.

My take-away from Dustins blog is firstly that my API also accepts date as a parameter and i therefore tried sending the RangeStart and RangeEnd parameters directly rather than through steps in Power Query. This seems to have done the trick and i now get historical data as well.

For my particular API the date form has to be in "yyyyMMdd" format why i converted the date parameters accordingly:

rngStart = DateTime.ToText(RangeStart,"yyyyMMdd"),
rngEnd = DateTime.ToText(RangeEnd,"yyyyMMdd"),

then passing those directly to the API query:

Query = [#"sQueryKey" = QueryKeyJob,#"FilterLocation"="Database",#"Filter"="Date>='"&rngStart&"' AND Date<'"&rngEnd&"'"],

Appreciate your assistance

Best regards,

Troels