Cannot set scheduled refresh for odata with parameter in URL
Hello Power BI Community,
I have built a simple power query to retrieve data from a given odata URL. It is data from SAP, shown in XML format instead of Json. Here is the query that I used.
URL= "http://abcd/cmd/odata/CC080?client=130&$filter=CreateDate ge datetime'" & DateTime.ToText(RangeStart, [Format="yyyy-MM-ddThh:mm:ss"]) & "'",
Source_Odata = Odata.Feed(URL, null, [Implementation="2.0"]),
#"Filtered Rows" = Table.SelectRows(Source_Odata, each [CreateDate] >= RangeStart and [CreateDate] < RangeEnd)
The paramenter "RangeStart" is used in the URL is to obtain only the incremental data. The purpose is to ensure that less data is obtained from the data source which will improve the performance.
The "Filtered Rows" step is to set up incremental refresh in PowerBI.
The incremental refresh can be set up in Desktop. But when I upload this pbi file to PowerBI Service, I got this error in the dataset setting page, saying that I cannot set up scheduled refresh.
I did some investigation but cannot find out the solution. It seems that this is caused by the parameter used in the Odata URL, which makes the URL dynamic. If so, this means if I need to set up the scheduled refresh, I cannot use the parameter in the URL. But due to the large amount of the data, I really need to filter only the necessary data at the first step in the power query to reduce the traffic and improve the performance. Is there a way to work this out?
I took a look at your link and changed my code. (Sorry for the capture since I wasn't able to copy and paste.)
But it returned an error.
I found the following link, saying that
"in order to append system query options to the URi used into OData.Feed , you must append them directly to the main URi. Or you can use Web.Contents which accepts system query options into the 'Query=[...]' clause."