Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jinlei19906
Frequent Visitor

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. 

 

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. 

jinlei19906_0-1680678442236.png

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

1 ACCEPTED SOLUTION

Have you already read this article?

Using OData.Feed and the Query option to avoid the dynamic data sources error in Power BI (crossjoin...

 

Otherwise, you can switch to Web.Contents with RelativePath/Query and create a function to handle the xml extraction.

Pat

Microsoft Employee

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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.

@lbendlin 

 

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

jinlei19906_2-1681091195447.png

 

 

But it returned an error.

jinlei19906_3-1681091195446.png

 

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?

Using OData.Feed and the Query option to avoid the dynamic data sources error in Power BI (crossjoin...

 

Otherwise, you can switch to Web.Contents with RelativePath/Query and create a function to handle the xml extraction.

Pat

Microsoft Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors