Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adwilliams
Frequent Visitor

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? 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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






Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
adwilliams
Frequent Visitor

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
Super User
Super User

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






Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors