In my last post I showed how, in many cases, you can avoid the “dynamic data sources” error with OData data sources by taking advantage of query folding. That’s not always possible though and in this post I’ll show you how you can use the Query option of the OData.Feed function to do so instead.
At first glance the Query option of OData.Feed looks very much like the Query option on Web.Contents which, of course, can also be used to avoid the dynamic data sources error (see here and here) and that’s true up to a point: you can use it to add query parameters to your OData URL. However the documentation is not particularly detailed and there is one thing that will confuse you when you try to use it: you can’t use it with OData system query options like $filter. For example, let’s say you wanted to query the People entity in the TripPin sample OData endpoint to get only the people whose first name was Scott. You can do this as follows:
let
Source = OData.Feed(
"https://services.odata.org/TripPinRESTierService/People?$filter=FirstName eq 'Scott'",
null,
[Implementation = "2.0"]
)
in
Source
Since $filter is a url query parameter, you might then try the following code:
let
Source = OData.Feed(
"https://services.odata.org/TripPinRESTierService/People",
null,
[
Implementation = "2.0",
Query = [
#"$filter"
= "FirstName eq 'Scott'"
]
]
)
in
Source
Unfortunately, this returns the following error:
Expression.Error: OData.Feed custom query options cannot start with ‘$’.
This is Power Query’s way of telling you you can’t use OData system query options with the OData.Feed Query option, since they always start with a $ sign. This is a deliberate design decision on the part of the Power Query team; I won’t go into the reasons why it is this way, it’s complicated!
However, you can use the Query option with OData custom query options like so:
let
Source = OData.Feed(
"https://services.odata.org/TripPinRESTierService/People",
null,
[
Implementation = "2.0",
Query = [#"debug-mode" = "true"]
]
)
in
Source
More importantly, you can use the Query option with OData parameter aliases. The original URL we were looking at:
https://services.odata.org/TripPinRESTierService/People?$filter=FirstName eq 'Scott'
…can be rewritten to use parameter aliases like so:
https://services.odata.org/TripPinRESTierService/People?$filter=FirstName eq @fn&@fn='Scott'
…and the rewritten version can be called using the Query option like so:
let
Source = OData.Feed(
"https://services.odata.org/TripPinRESTierService/People?$filter=FirstName eq @f",
null,
[
Implementation = "2.0",
Query = [#"@f" = "'Scott'"]
]
)
in
Source
You can use parameter aliases to call OData unbound functions too. For example, you can call TripPin’s GetNearestAirport function like so:
let
Source = OData.Feed(
"https://services.odata.org/TripPinRESTierService/GetNearestAirport(lat = @latitude, lon = @longitude)",
null,
[
Implementation = "2.0",
Query = [
#"@latitude" = "100",
#"@longitude" = "0"
]
]
),
#"Converted to Table" = Record.ToTable(
Source
)
in
#"Converted to Table"
Finally, here’s an example of a query that starts with a table of latitudes and longitudes and calls the GetNearestAirport function for each row:
let
Source = #table(
type table [
LocationLatitude = text,
LocationLongitude = text
],
{
{"10", "10"},
{"33", "-118"},
{"100", "-100"}
}
),
#"Added Custom" = Table.AddColumn(
Source,
"FunctionCall",
each OData.Feed(
"https://services.odata.org/TripPinRESTierService/GetNearestAirport(lat = @latitude, lon = @longitude)",
null,
[
Implementation = "2.0",
Query = [
#"@latitude" = [
LocationLatitude
],
#"@longitude" = [
LocationLongitude
]
]
]
)
),
#"Expanded FunctionCall"
= Table.ExpandRecordColumn(
#"Added Custom",
"FunctionCall",
{"Name", "IcaoCode", "IataCode"},
{
"FunctionCall.Name",
"FunctionCall.IcaoCode",
"FunctionCall.IataCode"
}
)
in
#"Expanded FunctionCall"
When you first publish a dataset with this query in to the Power BI Service you’ll see an internal server error message coming from the TripPin OData service. You can make the dataset refresh successfully and avoid this and the dynamic data sources error though: you need to check the Skip Test Connection box in the credentials dialog you can open from the Settings pane and set the data privacy level on the data source that is sending data to the OData function appropriately too:
Is it possible to pass a PowerQuery parameter to only retrieve records greater than a given date ? I am trying to dynamically filter a table based on its ‘createdon’ date field. I am using OData.Feed and I only wish to retrieve last 1 year of data from today. Currently I am writing $filter=createdon gt 2022-01-01″, null, [Implementation=”2.0″]. However I want to replace 2022-01-01 with a date passed on as a parameter. Thanks in advance.
Hi PBIDev, did you get this to work? I’m having a similar struggle:
hi PBIDev, did you ever get this to work? Struggling here with the same issue. On Desktop, this works:
But not when publishing to service. Getting the dynamic data source error.
For OData,
You need to have your date converted to a particular format first before you pass that value in your api call.
Convert date value into ‘YYYY-MM-DDThh:mm:ss’ format first.
Convert using: – DateTime.ToText(Source,[Format = ” YYYY-MM-DDThh:mm:ss “])
Now based on this format, you can also have your format based on what you don’t need.
So, if you don’t need to use the time then just YYYY-MM-DD should be fine. If you don’t need the date then just YYYY-MM will also work.
Curr_DateTime =
let
Source = DateTime.LocalNow(),
#”convert” = DateTime.ToText(Source,[Format = “yyyy-MM”])
date_6_months_before =
let
Source = DateTime.LocalNow(),
#”subtract_month” = Date.AddMonths(Source, -6), //Subtracted 6months from today’s date, so we get the exact 6 months as range.
#”convert” = DateTime.ToText(#”subtract_month”,[Format = “yyyy-MM”])
Now, suppose in the api call,
StartDate gt datetime'”&date_6_months_before&”‘ and StartDate le datetime'”&Curr_DateTime&”‘”)
Hello,
any idea how to solve this error?
DataSource.Error: OData: The feed’s metadata document appears to be invalid. Error: The metadata document could not be read from the message content.
UnexpectedXmlAttribute : The attribute ‘CollectionKind’ was not expected in the given context. : (1, 7754)
is there any Option for OData.Feed to ignore this error?
I don’t know – it could be a problem with the implementation of the OData feed you’re connecting to, or maybe you need to experiment with one of the other options on OData.Feed.
Thanks for the article it helps a lot, was trying to work out if there was a solution to dynamic source issue if the dynamic values are not in the parameter but query itself. E.g. https://services.odata.org/TripPinRESTierService/People?$filter=FirstName eq @f and instead of people I were to pass different names.
I may have found a way for it. However, this uses Web API connector and not OData.Feed. You can pass the query as dynamic reference in the relative path section. I tested this approach with D365 data. Since Web API for D365 limits data to 10000 records, I had to create pagination.
Below are the advanced queries if that helps:
Total Page count(Required for pagination):
let
Source = Table.FromColumns(
{Lines.FromBinary(
Web.Contents(
“https:{my company masked}dynamics.com/data/”,
[RelativePath = EntityName&”/$count?”& filterQuery &”&cross-company=”& CrossCompany]), null, null, 932)}),
#”Renamed Columns” = Table.RenameColumns(Source, {{“Column1”, “TotalRecords”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”, {{“TotalRecords”, type number}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “TotalpageNo”, each Number.RoundUp([TotalRecords]/10000)),
#”Removed Columns” = Table.RemoveColumns(#”Added Custom”, {“TotalRecords”}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Removed Columns”, {{“TotalpageNo”, Int64.Type}})
in
#”Changed Type1″
And this gives me all the records,
let
Source = if PageControl= “Page 1 Only” then {1..1} else {1..TotalPageCount{0}[TotalpageNo]},
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”, {{“Column1”, “PageNo”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”, {{“PageNo”, Int64.Type}}),
#”Added skip” = Table.AddColumn(#”Changed Type”, “skip”, each ([PageNo]-1)*10000),
#”skip type text” = Table.TransformColumnTypes(#”Added skip”, {{“skip”, type text}}),
#”Added top” = Table.AddColumn(#”skip type text”, “top”, each 10000),
#”top type text” = Table.TransformColumnTypes(#”Added top”, {{“top”, type text}}),
#”Added Custom” = Table.AddColumn(#”top type text”, “Custom”, each Json.Document(
Web.Contents(
“https://{my company name}dynamics.com/data”,
[RelativePath = EntityName & “?” & filterQuery & “&cross-company=” & CrossCompany &”&$skip=”&[skip]&”&$top=”&[top]]))),
#”Removed Other Columns” = Table.SelectColumns(#”Added Custom”, {“PageNo”, “Custom”}),
#”Expanded Custom” = Table.ExpandRecordColumn(#”Removed Other Columns”, “Custom”, {“value”}, {“value”}),
#”Expanded value” = Table.ExpandListColumn(#”Expanded Custom”, “value”),
Lastly, you can either have parameters or field reference. In my case I utilized both. This works really well and I haven’t had issues. For data sources that does not allow $skip, you may need to engineer a different pagination approach.
In my case, EntityName (table name), $filter and cross-company were parameters defined outside the table whereas $top and $skip were row items. I tested and found that it works with external (explicit parameters) or field value references.
Before you save though, make sure to click on Allow data referencing across tables under Options>>Privacy (assuming you are doing this in dataflow, similar options do exist in PBI Desktop)
I summarized everything here: https://sites.google.com/view/powerquerywizard/pagination-and-dynamic-query-for-d365-odata-entities-in-power-bi
Awesome …. have spent hours trying to get round this …. thank you !
This looks really useful but I must be missing something. The final solution for your People filter still has the keyword $filter in the URL. Doesn’t this $ sign indicate a system query option which therefore cannot be used?
(I am having an analgous problem with $top and $skipToken with Web.Contents)
It has a $ sign but it isn’t being used with the Query option in Web.Contents – the values are being passed in to parameters, which don’t start with $ but with @
Where did the upvote button go? Great article!