Using dynamic URL with OData.Feed

[nok]

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.

[kushanNa · Accepted solution]

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

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

[johnbasha33]

Hi,
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:

1. Go to Dataflows → Click Manage Parameters.

2. Create a parameter:

   • Name: StartDate

   • Type: Text

   • Default Value: "2022-03-01T00:00:00.99Z"

3. 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 !!
   
   

[nok]

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.