pass date parmeter i do two query for start and end date

[kheira]

hi i want to pass date parmeter  i do two query for start and end date
but i have this eror :
Expression.Error: We cannot convert the value "17-10-2020" to type Date. Details: Value=17-10-2020 Type=[Type]
 query a:
  let CreatedDateFrom = Date.AddDays(Date.EndOfDay(DateTime.LocalNow()),-10), #"Extracted Date" = Date.From(CreatedDateFrom), #"FormatDate" = Date.ToText(#"Extracted Date","dd-MM-yyyy") in #"FormatDate"
query b=
let CreatedDateFrom = Date.AddDays(Date.EndOfDay(DateTime.LocalNow()),0), #"Extracted Date" = Date.From(CreatedDateFrom), #"FormatDate" = Date.ToText(#"Extracted Date","dd-MM-yyyy") in #"FormatDate"
and this is a part :
let
sd=Date.ToText(a,"yyyy-MM-dd"),
es=Date.ToText(b,"yyyy-MM-dd"),
Source = Json.Document(Web.Contents("https://api.zira.us/external/metersData?meterIds=16127&stardate"&sd&"&enddate="&ed&"", [Headers=[username="**", password="**"]])),
data = Source[data],

[PhilipTreacy]

Hi @kheira 

I've simplified the code down into 1 query

let
    Date_A = Date.AddDays(Date.EndOfDay(DateTime.LocalNow()),-10),
    Extract_Date_A = Date.From(Date_A),
    sd = Date.ToText(Extract_Date_A,"yyyy-MM-dd"),

    Date_B = Date.AddDays(Date.EndOfDay(DateTime.LocalNow()),0),
    Extract_Date_B = Date.From(Date_B),
    es = Date.ToText(Extract_Date_B,"yyyy-MM-dd"),

    Source = Json.Document(Web.Contents("https://api.zira.us/external/metersData?meterIds=16127&startdate="&sd&"&enddate="&es&"", [Headers=[username="**", password="**"]])),
    data = Source[data]
in
    data

Create a new blank query and copy/paste this code into it.

Or download the PBIX file

The query creates a URL like this

https://api.zira.us/external/metersData?meterIds=16127&startdate=2020-10-17&enddate=2020-10-27

but that URL is generating an Internal Server Error 500 on the Zira web server.  I don't know why that would be, I'm not familiar with the Zira API.

You'll need to check the Zira API documentation to make sure the URL is constructed correctly, you are authenticating properly etc.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[PhilipTreacy]

Hi @kheira 

You're already converting the dates to text in queries a and b. So when you do this

sd=Date.ToText(a,"yyyy-MM-dd"),
es=Date.ToText(b,"yyyy-MM-dd"),

PQ is expecting a date in a and b,not text. 

Change the Date.ToText format in a and b to

Date.ToText(#"Extracted Date","yyyy-MM-dd") in #"FormatDate"

and make the main query

let
Source = Json.Document(Web.Contents("https://api.zira.us/external/metersData?meterIds=16127&stardate"&a&"&enddate="&b&"", [Headers=[username="**", password="**"]])),
data = Source[data],

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.