cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qihuiyu
Regular Visitor

Incremental Refresh with Odata

We are currently working on a Odata - Power BI based reporting solution for our clients.

We would like to make Incremental Refresh works on our odata connector.

 

I found this post https://community.powerbi.com/t5/Power-Query/Incremental-Refresh-with-OData/m-p/968901#M33400

on Nov. 2020, Odata is not officially supported yet for incremental refresh because of query folding supports.

 

However according to https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

Odata have $filter operator and should not be difficult to make query folding possible.

 

My question is, is Microsoft Power BI team planning to make Incremental Refresh possible for Odata data source soon?

Is it in your current roadmap or do you have expected release date? 

 

Thank you.

 

PS:

- OdataV4 implies that date time edm objects are DateTimeOffset (with timezone), so we can not have a date column of date/time, we can only have date/time/timezone data type. So on our side we are not seeing the query generated with $filter dates

- We implemented a workaround similar to this post: https://thinkaboutit.be/2020/02/how-do-i-implement-an-incremental-refresh-in-power-bi-free-or-pro/

6 REPLIES 6
qihuiyu
Regular Visitor

111.PNG

 

I try concatenate my url like this with & (and also Text.Cobine). The query has no syntax error and it actually works, it did refresh data with filter and load correctly when I click <close and apply>.

However it can not load in the query editor interface, I saw the warning sign and the query result table is empty. 

 

PS. I have a connector function which wraps the odata.feed and accept other oauth params.

It also cause problems when I publish my report to service and try to configure gateway, I have my url in DataSourcePath empty:  extensionDataSourcePath":"{\"url\":\"\",\ ....

The warning sign probably just says that you didn't let the preview finish.  Otherwise please post the error messsage.

It actually has no error message. The area is empty. If I remove the concatenated part, only the url root, it works again and table is loaded.

qihuiyu
Regular Visitor

Hi, we tried the following query:

let
Source = Odata.Feed("https://server/odata/TimeSeriesRawResults"),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= RangeStart)
in
#"Filtered Rows"

 

However on the sever log we dont see pbi sending request with $filter dates. 

 

You need both RangeStart and RangeEnd, and they need to be included as filters in your ODATA feed URL, not in subsequent steps.

lbendlin
Super User
Super User

Query folding is not a hard requirement for incremental refresh.  The DateTime field however is.  so you need to use Power Query to convert your DateTime RangeStart and RangeEnd values to DateTimeZone values if your ODATA source insists on that.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors