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
lischach
Employee
Employee

Pull full OData dataset only in PBI service and not in Desktop

I want to create a PBI dataset based on an OData feed. This OData feed returns a very large response (~80GB) so I don’t want to pull all this data in PBI desktop, but rather pull it only in the PBI service in the cloud.

I tried this power query:

 

let
Source = OData.Feed(Text.Combine({"<ODataFullUrl>", #"top_1000_items"}), null, [Implementation="2.0"])
in
Source

 


and setting locally the parameter `top_1000-items` to be `?$top=1000`, and in the cloud I want to set it to be `?`.

This doesn’t work since this way, when I try to refresh the data in the cloud I get this error:

 

Something went wrong
This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

Please try again later or contact support. If you contact support, please provide these details.

 


I also tried changing my query to this:

 

 

let
Source = OData.Feed("<ODataFullUrl>", null, [Implementation="2.0", Query = [ #"$top" = "1"]])
in
Source

 

But then I get this error saying I can't use query parameters that start with `$`:

Expression.Error: OData.Feed custom query options cannot start with '$'.

 

I understood from this page: Data refresh in Power BI - Power BI | Microsoft Docs , that this is considered a dynamic data source, and if I want to avoid dynamic data source, I need to use Web.Contents M function, but I'm trying to connect to an OData feed, which returns paged data, meaning it returns "odata.nextLink" property in the response with the url for the next request, and PBI automatically calls this to fetch the next results, so I'm not sure how to mimic this behavior uisng Web.Contents M function.

 

Is there anyone who has done this before and can share details regarding how to acheive this goal?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

To set a refresh on a dynamic data source, try using RelativePathHave a look at my blogpost here! Hope this helps!

View solution in original post

4 REPLIES 4
robertCharlton
Frequent Visitor

By the way - the thing about parameters not working with the $ sign can be resolved using percent encoding: 

eg  #"%24top" instead of #"$top"

 

Anonymous
Not applicable

To set a refresh on a dynamic data source, try using RelativePathHave a look at my blogpost here! Hope this helps!

You *should* be able to use RelativePath, but RelativePath isn't implemented on Odata.Feed.

I put up an idea about that - and you could vote for the idea here: Microsoft Idea · Odata.Feed should have RelativePath as an option the same as Web.Contents. (powerbi...

lbendlin
Super User
Super User

use Incremental refresh (even if your data doesn't technically support it, and even if you hadn't planned to use it).  That allows you to specify only a small subset of the data on the desktop but will pull the entire data on the service.

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