From Web Connector - How can I add current date to a query?

[Anonymous]

I have a report that extracts data from Mixpanel.  Mixpanel allows you to call an API with from/to date parameters.

I can extract data if I hard code the dates, but I want to be able to set the to_date to be today.

Query that works:

https:// <<secret_key>> @mixpanel.com/api/2.0/segmentation?from_date=2020-04-01&to_date=2020-04-28&e... of Us Marketing Landing Page

I tried this, but I'm getting an error with the format:

https://<<secret_key>> @mixpanel.com/api/2.0/segmentation?from_date=2020-04-01&to_date=`date +%y/%m/%d`&event=All of Us Marketing Landing Page

I can't find anything in the documentation that supports this.  Any help would be appreciated.

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could use dynamic parameter to define your to_date. You need to set it in the query editor.

Here is the m query for your reference.

let
    localdate = Text.Format("#{0}-#{1}-#{2}",{Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),Date.Day(DateTime.LocalNow())}),
    test=Json.Document(Web.Contents("https:// <<secret_key>> @mixpanel.com/api/2.0/segmentation?from_date=2020-04-01&to_date="&localdate&"&event=All of Us Marketing Landing Page"))
in
    Source

[Anonymous]

Thanks, using a dynamic parameter was the way to go.