Forum Discussion
OData Incremental refresh compatible with schedule refresh
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
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".
Pat
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
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
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
