Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
To set a refresh on a dynamic data source, try using RelativePath. Have a look at my blogpost here! Hope this helps!
By the way - the thing about parameters not working with the $ sign can be resolved using percent encoding:
eg #"%24top" instead of #"$top"
To set a refresh on a dynamic data source, try using RelativePath. Have 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...
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.