Forum Discussion

lgs2024's avatar
lgs2024
Regular Visitor
2 years ago

Using date variable in advanced editor returning empty value

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/2854864

 

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

 

 

Need Help

4 Replies

    • lgs2024's avatar
      lgs2024
      Regular Visitor
      2 years ago

      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.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      lgs2024
      Regular Visitor
      2 years ago

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