Can I execute another M function within Json.FromValue() for a web API call ?

[adwilliams]

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? 

[adwilliams]

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.

[ImkeF · Accepted solution]

Hi @adwilliams ,
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