Forum Discussion
Using dynamic URL with OData.Feed
Hello!
I'm creating a dataflow that connects to an ODATA with dynamic date filters, but I can't save the dataflow with the error "One or more table references a dynamic Data Source". My connection in the Advanced Editor looks like this:
let
dateCurrentDay = DateTime.LocalNow(),
dateTwoYearsAgo = Date.AddYears(dateCurrentDay, -2),
dateFormat = DateTime.ToText(dateTwoYearsAgo, "yyyy-MM-ddTHH:mm:ss.99Z"),
Fonte = OData.Feed("https://api.myodata.com/xx/v7/TableRequest$filter=UpdatedAt gt cast(" & dateFormat & ",Edm.DateTimeOffset)", null, [Implementation="2.0"]),
Tabela = Table.StopFolding(Fonte)
in
Tabela
I tried using [RelativePath] in my dynamic URL, but it doesn't seem to work with OData.Feed.
Is there any way I can make a dynamic URL connecting to an ODATA? I'm not able to save my dataflow.
Hi nok
simialr issue :
looks like he Put the query pairs into the Query section, not the Relative path section. and got it sorted :https://community.fabric.microsoft.com/t5/Power-Query/Help-with-syntax-One-or-more-table-references-a-dynamic-Data/td-p/3134545
so adding only Relative path only is not sufficent it seems you will need to add the query section as well
check this arlticle as well :
Using OData.Feed And The Query Option To Avoid The Dynamic Data Sources Error In Power BI
so i'm guessing it must be something similar to this
let dateCurrentDay = DateTime.LocalNow(), dateTwoYearsAgo = Date.AddYears(dateCurrentDay, -2), dateFormat = DateTime.ToText(dateTwoYearsAgo, "yyyy-MM-ddTHH:mm:ss.99Z"), Source = OData.Feed( "https://api.myodata.com/xx/v7/TableRequest", null, [ Implementation = "2.0", Query = [#"@updatedDate" = "cast(" & dateFormat & ",Edm.DateTimeOffset)"] ] ) in Source
3 Replies
Hi nok
simialr issue :
looks like he Put the query pairs into the Query section, not the Relative path section. and got it sorted :https://community.fabric.microsoft.com/t5/Power-Query/Help-with-syntax-One-or-more-table-references-a-dynamic-Data/td-p/3134545
so adding only Relative path only is not sufficent it seems you will need to add the query section as well
check this arlticle as well :
Using OData.Feed And The Query Option To Avoid The Dynamic Data Sources Error In Power BI
so i'm guessing it must be something similar to this
let dateCurrentDay = DateTime.LocalNow(), dateTwoYearsAgo = Date.AddYears(dateCurrentDay, -2), dateFormat = DateTime.ToText(dateTwoYearsAgo, "yyyy-MM-ddTHH:mm:ss.99Z"), Source = OData.Feed( "https://api.myodata.com/xx/v7/TableRequest", null, [ Implementation = "2.0", Query = [#"@updatedDate" = "cast(" & dateFormat & ",Edm.DateTimeOffset)"] ] ) in SourceHi,
You're running into dynamic data source limitations in Power BI dataflows. Power BI restricts queries where parts of the URL (like date filters) are dynamically generated in M code, causing the "One or more tables reference a dynamic data source" error. This happens because the OData.Feed() function needs to be a foldable query for Power BI service to support scheduled refresh.Instead of dynamically generating the URL inside
let, use Power Query parameters:-
Go to Dataflows → Click Manage Parameters.
-
Create a parameter:
-
Name:
StartDate -
Type:
Text -
Default Value:
"2022-03-01T00:00:00.99Z"
-
-
Modify your query like this:
let
Fonte = OData.Feed("https://api.myodata.com/xx/v7/TableRequest?$filter=UpdatedAt gt cast('" & StartDate & "',Edm.DateTimeOffset)", null, [Implementation="2.0"]),
Tabela = Table.StopFolding(Fonte)
in
Tabela
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Hi, johnbasha33 .Thanks for the quick reply!
I just didn't understand the part about "Default Value: "2022-03-01T00:00:00.99Z"", because this example that you gave, the default value is a static date.
Using a parameter, isn't it possible to create a dynamic date in this default value? I want the dynamic date to always be exactly 2 years ago from the current date.
-
