Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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],
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.
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.
Proud to be a Super User!
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |