Using date variable in advanced editor returning empty value

[lgs2024]

Hi all

Fairly new to Power BI so hopefully I'm using the right terms etc. I found a similar thread with a different error but it doesn't appear to be resolved: https://community.fabric.microsoft.com/t5/Desktop/Defining-today-s-date-on-advanced-editor/td-p/2854...

I'm querying data from a REST API that requires today's date in a string format. Currently this is static so being updated manually, but now the dashboard is completed I'm trying to automate the date generation.

I have the below code which works fine by itself when I run it as its own query:

Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),0),"yyyy-MM-ddTHH:mm:ss")

When I use the above within the API query it seems to output nothing so the table outputs no data due to the missing date string:

I have tried tried inserting the code directly into the API query and also via a variable however I get the same result as above. I have tried escaping the quotes but this doesn't change the outcome either.

Here is a working (manual) example - the highlighted portion being what I need to automatically replace:

Thanks in advance for any assistance

[danextian]

Hi @lgs2024 ,

Did you replace the highlighted string directly with your pq formula or concatenate it with the rest of the strings? For example ...[" & pqformula & "]...  pqformula should be replaced by the actual formula.

[lgs2024]

Apologies for the ignorance, would you be willing to help with an expanded example?

I first tried it like this:

conditions = "doneFlag=false AND dateStart > [Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),0),""yyyy-MM-ddTHH:mm:ss"")]"

I'm not sure how I should be concatenating based on your example. The API formatting requires the date format to be within hard bracket characters [].

[amitchandak]

@lgs2024 , check how to pass dynamic parameter

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

[lgs2024]

Looks like a good approach - I have tried to use it in the format of the query I have but it doesn't appear to work. I have a considerable function performing the API query which allows the advanced editor to pass the conditions. Ideally I'm trying to find a way to evaluate the expression within double quotes without modifying the source function.