Forum Discussion
Can I execute another M function within Json.FromValue() for a web API call ?
Hello,
I have created a post request that sends JSON to a web API to return a list of records in Power BI. My current query looks something like this:
let
url = "baseURL"
header = [#"Content-Type"="application/json",#"Accept"="application/json"],
body = Text.FromBinary(Json.FromValue([metrics = {"NEW_REVIEWS"}, dimensions = {"DAYS"}, filters = [ startDate = #date(Date.Year(Date.From(Date.AddDays(DateTime.LocalNow(),-1))),Date.Month(Date.From(Date.AddDays(DateTime.LocalNow(),-1))),Date.Day(Date.From(Date.AddDays(DateTime.LocalNow(),-1)))), endDate = #date(Date.Year(Date.From(Date.AddDays(DateTime.LocalNow(),-8))),Date.Month(Date.From(Date.AddDays(DateTime.LocalNow(),-8))),Date.Day(Date.From(Date.AddDays(DateTime.LocalNow(),-8))))]])),
Source = Json.Document(Web.Contents(url,[Headers = header,Content=Text.ToBinary(body)]))
in
Source
I want to change my filters to utilize the series of nested functions Date.AddDays(DateTime.LocalNow(),-1) function to return data starting from yesterday. I have the tried a few different syntax all returning a proper date but I continue to receive a 400 Bad Request Error back from the API. My guess is that the post request is passing the literal instead of executing the function for today's date since it is wrapped in Json.FromValue().
Is there a way I can execute this function within the body of my post request this way?
Hi Anonymous ,
in general, you can get rid of some transformations, like so, as the Json.FromValue transforms that data into the needed binary form already:let url = "baseURL" header = [#"Content-Type"="application/json",#"Accept"="application/json"], body = Json.FromValue([metrics = {"NEW_REVIEWS"}, dimensions = {"DAYS"}, filters = [ startDate = #date(Date.Year(Date.From(Date.AddDays(DateTime.LocalNow(),-1))),Date.Month(Date.From(Date.AddDays(DateTime.LocalNow(),-1))),Date.Day(Date.From(Date.AddDays(DateTime.LocalNow(),-1)))), endDate = #date(Date.Year(Date.From(Date.AddDays(DateTime.LocalNow(),-8))),Date.Month(Date.From(Date.AddDays(DateTime.LocalNow(),-8))),Date.Day(Date.From(Date.AddDays(DateTime.LocalNow(),-8))))]]), Source = Json.Document(Web.Contents(url,[Headers = header,Content=body])) in Source
Then, it could be that you need to transform the dates into text for the API to work correctly. The Date.ToText function gives you some options to apply the correct format (XXXXXXX):let url = "baseURL" header = [#"Content-Type"="application/json",#"Accept"="application/json"], body = Json.FromValue([metrics = {"NEW_REVIEWS"}, dimensions = {"DAYS"}, filters = [ startDate = Date.ToText(#date(Date.Year(Date.From(Date.AddDays(DateTime.LocalNow(),-1))),Date.Month(Date.From(Date.AddDays(DateTime.LocalNow(),-1))),Date.Day(Date.From(Date.AddDays(DateTime.LocalNow(),-1)))), XXXXXXXX), Date.ToText(endDate = #date(Date.Year(Date.From(Date.AddDays(DateTime.LocalNow(),-8))),Date.Month(Date.From(Date.AddDays(DateTime.LocalNow(),-8))),Date.Day(Date.From(Date.AddDays(DateTime.LocalNow(),-8)))), XXXXXXXX)]]), Source = Json.Document(Web.Contents(url,[Headers = header,Content=body])) in Source
2 Replies
Thanks for the suggestion to clean up the Text.toBinary, I've removed that!
I realized that nothing was wrong with my syntax and I do not even need to use the Date.ToText(). I had mixed up the Date.AddDays for my startDate and endDate (my end date needed to be after my startDate and i had the -1 and -8 days backwards! small mistake that was specific.
Hi Anonymous ,
in general, you can get rid of some transformations, like so, as the Json.FromValue transforms that data into the needed binary form already:let url = "baseURL" header = [#"Content-Type"="application/json",#"Accept"="application/json"], body = Json.FromValue([metrics = {"NEW_REVIEWS"}, dimensions = {"DAYS"}, filters = [ startDate = #date(Date.Year(Date.From(Date.AddDays(DateTime.LocalNow(),-1))),Date.Month(Date.From(Date.AddDays(DateTime.LocalNow(),-1))),Date.Day(Date.From(Date.AddDays(DateTime.LocalNow(),-1)))), endDate = #date(Date.Year(Date.From(Date.AddDays(DateTime.LocalNow(),-8))),Date.Month(Date.From(Date.AddDays(DateTime.LocalNow(),-8))),Date.Day(Date.From(Date.AddDays(DateTime.LocalNow(),-8))))]]), Source = Json.Document(Web.Contents(url,[Headers = header,Content=body])) in Source
Then, it could be that you need to transform the dates into text for the API to work correctly. The Date.ToText function gives you some options to apply the correct format (XXXXXXX):let url = "baseURL" header = [#"Content-Type"="application/json",#"Accept"="application/json"], body = Json.FromValue([metrics = {"NEW_REVIEWS"}, dimensions = {"DAYS"}, filters = [ startDate = Date.ToText(#date(Date.Year(Date.From(Date.AddDays(DateTime.LocalNow(),-1))),Date.Month(Date.From(Date.AddDays(DateTime.LocalNow(),-1))),Date.Day(Date.From(Date.AddDays(DateTime.LocalNow(),-1)))), XXXXXXXX), Date.ToText(endDate = #date(Date.Year(Date.From(Date.AddDays(DateTime.LocalNow(),-8))),Date.Month(Date.From(Date.AddDays(DateTime.LocalNow(),-8))),Date.Day(Date.From(Date.AddDays(DateTime.LocalNow(),-8)))), XXXXXXXX)]]), Source = Json.Document(Web.Contents(url,[Headers = header,Content=body])) in Source