OData Incremental refresh compatible with schedule refresh

[Ronan-T]

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

[ppm1]

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....

Pat

[Ronan-T]

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

[ppm1 · Accepted solution]

Check out the second approach in this article that shows an example of using List.Generate to recursively use a nextlink type url to page through responses until the last one. The key step is the one called datalist.

Updated – Get SharePoint List Data … Fast – Hoosier BI

Pat

[Ronan-T]

Thank you Pat ! 

Here is my code if someone else needs it.

I've splitted the query in two because in the transformation step (the one that convert the list of response to a table) was failing in Power BI Service. Probably due to empty response.  Error was saying "Column1" (the field where the list is stored) did not exists

So in order to setup incremental refresh on the final table, I kinda tricked PowerBI by filtering the final result using RangeStart and RangeEnd, but I think it could be more effiencient to just use a dummy step

"myRessource Raw" M code:

(Get all the API pages using paginate_field)

let
    base_url = "https://myurl.com/odata/1.0/" , 
    ressource_path = "myRessources",
    relative_path = technical_workspace & "/" & ressource_path,
    date_field = "createdAt",
    paginate_field = #"@odata.nextLink",
    InitialWebCall = Json.Document(
                Web.Contents(
                    base_url ,
        [
            RelativePath = relative_path,
            Query=[#"$filter" = date_field & " ge '" & DateTime.ToText(RangeStart, "yyyy-MM-dd") & "' and " & date_field & " lt '" & DateTime.ToText(RangeEnd, "yyyy-MM-dd") & "'" ]
        ]
        )
    ),
    Datalist = List.Generate(
        () => InitialWebCall, 
        each List.Count([value]) > 0,
        each 
          try 
            Json.Document(
                Web.Contents(base_url, [
                    RelativePath = relative_path & Text.AfterDelimiter([paginate_field], ressource_path)
            ])) 
          otherwise [value = {}], 
        each [value])
    
in
    Datalist

"myRessource" code :

(Convert the list of response to table data)

let
    Source = #"myRessources Raw",
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"), //this is probably this step that was failing in Power BI Service
    #"Column1 Expand Record" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"_id", "attributeName", "event_id", "attributeFilter", "attributeValue", "createdAt"}, {"_id", "attributeName", "event_id", "attributeFilter", "attributeValue", "createdAt"}),
    #"Modified Type" = Table.TransformColumnTypes(#"Column1 Expand Record",{{"createdAt", type datetime}}),
    #"Filter Lines" = Table.SelectRows(#"Modified Type", each [receivedAt] >= RangeStart and [receivedAt] < RangeEnd) //step added to tricks Power BI in order to be able to setup incremental refresh
in
    #"Filter Lines"

This way it works really well, I can schedule refresh in Power BI Service and incremental refresh seems to work just fine