Can not add "$filter" operation in URL with variable when use OData Feed.

[chengzong]

Hi Team,

I am use OData Feed to fetch Data from the API.

let
    apiURL = "https://api.xxxx.com/Users?$filter=ModifiedDate gt 2024-11-18T12:00:00Z",
    apiHeaders = [
        #"Authorization" = access_token
    ],
    Source = OData.Feed(apiURL, null, [Headers=apiHeaders])
in
    Source

 This piece of code works. But when I put the date into a variable. 

let
    apiURL = "https://api.xxxx.com/Users?$filter=ModifiedDate gt " &   formattedStartDate ,
    apiHeaders = [
        #"Authorization" = access_token
    ],
    Source = OData.Feed(apiURL, null, [Headers=apiHeaders])
in
    Source

I got a waring "Some data sources may not be listed because of hand-authored queries" in Data Source settings.

And this can be refreshed in local PowerBI Desktop. But after I published to the Cloud. Failed to refreshe the data.

 

[chengzong · Accepted solution]

Hi @danextian .
Thanks for your replay, but this issue still exist.

One interesting thing is that when I use a constant string or set the formattedStartDate as a string parameter. It works. 
But when I use the code to generate this variable, or set the type of the parameter to Date/Time. This issue still exist.

currentDateTime = DateTime.LocalNow(),    
formattedStartDate = DateTime.ToText(currentDateTime, "yyyy-MM-ddThh:mm:ssZ"),

By the way, I find a workaround, I do not know the reseaon, but it works for me.

    Source = OData.Feed(
        "https://api.xxxx.com/Users?$filter=ModifiedDate gt @testdate", 
        null, 
        [
            Query=[#"@testdate" = formattedStartDate], 
            Headers=apiHeaders])

REF: https://blog.crossjoin.co.uk/2023/01/15/using-odata-feed-and-the-query-option-to-avoid-the-dynamic-d...

[Anonymous]

Hi @chengzong 

It is glad that you solve  your problem ,you can consider to accept your answer as solution so that more user can refer to.

Best Regards!

Yolo Zhu

[danextian]

Hi @chengzong 

That is expected as what you did created a dyanmic data source - https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data#refresh-and-dynamic-data-source... . Use relative path

let
    baseURL = "https://api.xxxx.com/",
    relativePath = "Users?$filter=ModifiedDate gt " &   formattedStartDate",
    fullURL = baseURL & relativePath,
    apiHeaders = [
        #"Authorization" = access_token
    ],
    Source = OData.Feed(fullURL, null, [Headers = apiHeaders])
in
    Source