Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
let
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)
in
#"Filtered Rows"
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?
Best regards,
Lei
Solved! Go to Solution.
Have you already read this article?
Otherwise, you can switch to Web.Contents with RelativePath/Query and create a function to handle the xml extraction.
Pat
Please familiarize yourself with the second parameter.
OData.Feed - PowerQuery M | Microsoft Learn
Especially the Query part. It will allow you to have a static base URL, and will most likely make the scheduled refresh work.
Thank you for the reply!
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."
OData-Feed-command-with-query-and-options-no-working
So I also tried to use the Odata query in a way shown in the following example, but a "bad request" error returned, so I guess my data source does not support this format.
let Source = OData.Feed( "https://services.odata.org/TripPinRESTierService/People?$filter=FirstName eq @f", null, [ Implementation = "2.0", Query = [#"@f" = "'Scott'"] ] ) in Source
Based on this, I think the only way I can go now is to use the web.contents, although my code will be much more complecated since I need to expand the XML file...
Do you have any suggestions or comments?
Best regards,
Lei
Have you already read this article?
Otherwise, you can switch to Web.Contents with RelativePath/Query and create a function to handle the xml extraction.
Pat
Thank you for the advice. I tried the way in the link but I think my odata version does not support the use of parameter alias. I solved this by using the web contents instead.