Forum Discussion

Ronan-T's avatar
Ronan-T
Frequent Visitor
3 years ago
Solved

OData Incremental refresh compatible with schedule refresh

Hi,

I have a OData feed and I need to setup incremental refresh in order to load all the data

 

First I have setup a custom filters like that

 

let
    Source = OData.Feed("https://myurl.net/ressource", null, [Implementation="2.0"]),
    #"Filtered Lines" = Table.SelectRows(Source, each [receivedAt] >= RangeStart and [receivedAt] < RangeEnd)
in
    #"Filtered Lines"

 

 

 

Schedule Refreshed worked like a charm except I couldn't setup incremental refresh (unlike others connectors for exemple).

 

So I changed the query to look like this.

 

let
    strRangeStart = DateTime.ToText(RangeStart,[Format="yyyy-MM-dd'T'HH:mm:ss'Z'", Culture="en-US"]),
    strRangeEnd = DateTime.ToText(RangeEnd,[Format="yyyy-MM-dd'T'HH:mm:ss'Z'", Culture="en-US"]),
    Source = OData.Feed("https://myurl.net/ressource?$filter=createdAt ge '" & strRangeStart &"' and createdAt lt '" &  strRangeEnd & "'" , null, [Implementation="2.0"])
in
    Source

 

With this I can set up incremental refresh on PowerBI desktop but once I publish I can't schedule refresh.

I seems that DateTime.ToText break query folding (lol?)

 

It seems weird to have schedule refresh disabled since you can actually create source like that 

 

let 
   Source = OData.Feed("https://" & url & "/" & ressource, null, [Implementation="2.0"])  
in
   Source

 

 

 

Am I missing something ?

Is there a way to implement an OData Connector that could support query folding ? or a workaround that could allow me to do both incremental refresh and scheduled refresh ?

 

Best Regards

5 Replies

  • ppm1's avatar
    ppm1
    Solution Sage

    You are doing manual "folding" by adding filter terms based on RangeStart and RangeEnd. It may work with incremental refresh, but first you'll have to leverage the RelativePath term in Web.Contents. See the article below. Once you get that working in desktop, publish it and when you go to "Edit Credentials" in the settings for the dataset when you set up Scheduled Refresh, check the box that says "Skip Test Connection".

    Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI (crossjoin.co.uk)

    Pat

    • Ronan-T's avatar
      Ronan-T
      Frequent Visitor

      Thank you for the tips 

      It's kinda working but Web.Contents doesn't seems to handle odata's pagination ( @odata.nextLink with skiptoken )

      Do you know how I could manage to automatically load all the page ?

       

      Regards