cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saikishore Visitor
Visitor

Passing Variables in API Post Request JSON Body

I'm using the google business API to get the driving directions insights

(auth as text, locid as text) =>
let
    AuthKey = "Bearer "& auth,
    url = "https://mybusiness.googleapis.com/v3/accounts/123456/locations:reportInsights",
    body = "{""locationNames"":[locid,],
""drivingDirectionsRequest"": {""numDays"":""NINETY"",},}",
    Source = Json.Document(Web.Contents(url,[
             
         Headers = [#"Authorization"=AuthKey ,
                    #"Content-Type"="application/json"],
         Content = Text.ToBinary(body) 
             ]   
        ))
in
    Source

This code throws up an bad request error.

This method works well if I hardcode the locid data as text into the query but fails when i use the locid variable to run the query for multiple entries.

Is there any way to declare a global variable in JSON body text??

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Passing Variables in API Post Request JSON Body

Hi @saikishore,

you don't use the parameter "locid" as text in your function at all. If your expression in step "body" is supposed to do this: It will not work. If locid is a single text string, you have to concatenate it like so:

 

"{""locationNames"":"& locid &",#(lf)""drivingDirectionsRequest"": {""numDays"":""NINETY"",},}"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




11 REPLIES 11
Community Support Team
Community Support Team

Re: Passing Variables in API Post Request JSON Body

Hi saikishore,

 

There may be something wrong the types of parameter you have predefined. Please check if type in power query matches type in JSON. The types of variables in M are like below:

{ any; none; null; logical; number; time; date; datetime; datetimezone; duration; text; binary; type; list; record; table; function; anynonnull }

 

For example, you can change type of locid with number and try again.

 

Best Regards,

Jimmy Tao

Super User
Super User

Re: Passing Variables in API Post Request JSON Body

Hi @saikishore,

you don't use the parameter "locid" as text in your function at all. If your expression in step "body" is supposed to do this: It will not work. If locid is a single text string, you have to concatenate it like so:

 

"{""locationNames"":"& locid &",#(lf)""drivingDirectionsRequest"": {""numDays"":""NINETY"",},}"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Highlighted
ericOnline Member
Member

Re: Passing Variables in API Post Request JSON Body

Hello @ImkeF@v-yuta-msft

 

The title of this post extactly matches my question. 

 

How can I pass key : value pairs from a table rather than 1 by 1?

 

Here is my example API call (works great):

 

let
    url = "https://company.com/api/path",
    headers = [
        "apikey" = thisisnotreallymyapikey
        "accept" = application/json,
        "Content-Type" = application/json
    ],
    content = "{
        ""key"": {
            ""nestedKey"": value1,
            ""nestedKey"": value2
        },
        ""key"": """",
        ""key"": ""value3"",
        ""key"": ""value4"",
        ""key"": value5,
        ""key"": [
{""key1"": ""value6"", ""key2"": ""value7""} ] }", webdata = Web.Contents(url, [Headers=headers,Content = Text.ToBinary(content)]), response = Json.Document(webdata) in response 

 

I need to have a table of key:value pairs that my query can hit iteratively until complete.

Example

  • Query refers to a table
  • Pulls all key:value pairs accordingly
  • Sends POST call to API
  • Saves data to a table
  • Repeats
  • Appends data to a table until all calls have been made.

    I don't know how to go about this. Any advice you can give is much appreciated.


Thank you

Super User
Super User

Re: Passing Variables in API Post Request JSON Body

Just to make sure to understand your request:

 

1) For me a recursive action means that it picks up something from a previous action in order to continue.

I cannot spot that in your example. So could it be that you mean "iteratively" instead?: Go through a table with parameters and pass one row of it into each API-call?

 

2) With some additional escape signs and indizes, your "content" can be parsed out as a nested Record like so: 

 

image.png

 

Will this structure always be the same for every key-value-pair combination that has to be passed trough (there are 9 different "Key"-fields in there currently)?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




ericOnline Member
Member

Re: Passing Variables in API Post Request JSON Body

Hello @ImkeF, thank you for the reply.

 

1. You are correct. I mistakenly used "recursive" rather than "iterative". 

2. Yes. Would like to use a table similar to below. Each record is a separate API call. I added numbers to the key:values above to show where in the query each cell would go.

 

apiTable.png

This is a little different than how you laid out the table (records vs. columns).

 

3. Yes, the structure will always be the same for each call. 

Super User
Super User

Re: Passing Variables in API Post Request JSON Body

You transform your query into a function ("MyFunction") that takes the current row (record) as its only argument. Then you add a column where you call "MyFunction" and pass the current record as a parameter to it:

 

Table.AddColumn(<PreviousStepname>, "APICall", each MyFunction(_))

 

quickest syntax with value1 as example (there might be issues with escaping, that you just have to try out..):

 

(_ as record) =>
let
    url = "https://company.com/api/path",
    headers = [
        "apikey" = thisisnotreallymyapikey
        "accept" = application/json,
        "Content-Type" = application/json
    ],
    content = "{
        ""key"": {
            ""nestedKey"": "& _[value1] &",
            ""nestedKey"": value2
        },
        ""key"": """",
        ""key"": ""value3"",
        ""key"": ""value4"",
        ""key"": value5,
        ""key"": [
            {""key1"": ""value6"", ""key2"": ""value7""}
        ]
    }",
    webdata = Web.Contents(url, [Headers=headers,Content = Text.ToBinary(content)]),
    response = Json.Document(webdata)
in
    response 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




ericOnline Member
Member

Re: Passing Variables in API Post Request JSON Body

Hi @ImkeF. Thank you for putting time and energy into this response. I was pulled off this Power BI project for a month or so, now I'm back and trying to reorient myself with this problem.

Will respond with results.

ericOnline Member
Member

Re: Passing Variables in API Post Request JSON Body

Hi @ImkeF

Working through your suggestion here. I'm missing something as I'm receiving a "Token Equal expected." error on the "Table.AddColumn() function.

 

I don't see any linting issues in the VS Code Power Query M Language extension. The first parenthesis in the Table.AddColumn() function is highlighted when I click "Show Error"

 

Thought I'd be missing a comma or parens., but I believe they're all in there...


Can you see what I'm missing?:

//Import spreadsheet

let Source = Excel.Workbook(File.Contents("C:\Users\me\apiLoadSheet.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type0" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"lon", type number}, {"lat", type number}, {"location_id", type text}, {"start_time", type datetime}, {"end_time", type datetime}, {"timestep", Int64.Type}, {"value1", type text}, {"value2", type text}, {"value3", type text}, {"value4", type text}, {"value5", type text}, {"value6", type text}, {"value7", type text}, {"value8", type text}, {"value9", type text}, {"value10", type text}, {"value11", type text}, {"value12", type text}, {"value13", type text}, {"value14", type text}, {"value15", type text}, {"value16", type text}, {"value17", type text}, {"value18", type text}, {"value19", type text}, {"value20", type text}, {"value21", type text}, {"value22", type text}, {"value23", type text}, {"value24", type text}, {"value25", type text}, {"value26", type text}, {"value27", type text}, {"value28", type text}, {"value29", type text}}),
//Start custom function to read records in table. Here is where the error is.
Table.AddColumn(#"Changed Type0", "APICall", each apiLoadSheetFunction(_)) (_ as record) => let url = "https://my.api.com", headers = [ #"apikey" = "myApiKey", #"accept" = "application/json", #"Content-Type" = "application/json" ], content = "{ ""geocode"": { ""lon"": -123.31177520752, ""lat"": 46.4498901367188 }, ""location_id"": """", ""start_time"": ""2017-11-01T12:00:00Z"", ""end_time"": ""2017-11-07T12:00:00Z"", ""timestep"": 60, ""fields"": [ {""name"": ""temp"", ""units"": ""F""}, {""name"": ""feels_like"", ""units"": ""F""}, {""name"": ""dewpoint"", ""units"": ""F""}, {""name"": ""wind_speed"", ""units"": ""mph""}, {""name"": ""wind_gust"", ""units"": ""mph""}, {""name"": ""baro_pressure"", ""units"": ""inHg""}, {""name"": ""visibility"", ""units"": ""km""}, {""name"": ""precipitation"", ""units"": ""in/hr""}, {""name"": ""cloud_cover"", ""units"": ""%""}, {""name"": ""cloud_ceiling"", ""units"": ""ft""}, {""name"": ""cloud_base"",""units"": ""ft""}, {""name"": ""humidity"", ""units"": ""%""}, {""name"": ""wind_direction"", ""units"": ""degrees""}, {""name"": ""precipitation_type""}, {""name"": ""sunrise""}, {""name"": ""sunset""} ] }", webdata = Web.Contents(url, [Headers=headers,Content = Text.ToBinary(content)]), response = Json.Document(webdata), Source = response in response

Thanks for your expertise on this matter!

Super User
Super User

Re: Passing Variables in API Post Request JSON Body

Hi @ericOnline,

please try this:

 

//Import spreadsheet

let 
    Source = Excel.Workbook(File.Contents("C:\Users\me\apiLoadSheet.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type0" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"lon", type number}, {"lat", type number}, {"location_id", type text}, {"start_time", type datetime}, {"end_time", type datetime}, {"timestep", Int64.Type}, {"value1", type text}, {"value2", type text}, {"value3", type text}, {"value4", type text}, {"value5", type text}, {"value6", type text}, {"value7", type text}, {"value8", type text}, {"value9", type text}, {"value10", type text}, {"value11", type text}, {"value12", type text}, {"value13", type text}, {"value14", type text}, {"value15", type text}, {"value16", type text}, {"value17", type text}, {"value18", type text}, {"value19", type text}, {"value20", type text}, {"value21", type text}, {"value22", type text}, {"value23", type text}, {"value24", type text}, {"value25", type text}, {"value26", type text}, {"value27", type text}, {"value28", type text}, {"value29", type text}}),

//Start custom function to read records in table. Here is where the error is.

apiLoadSheetFunction = (_ as record) =>

let
    url = "https://my.api.com",
    headers = [
        #"apikey" = "myApiKey",
        #"accept" = "application/json",
        #"Content-Type" = "application/json"
    ],
    content = "{
        ""geocode"": {
                    ""lon"": -123.31177520752,
                    ""lat"": 46.4498901367188
                },
                ""location_id"": """",
                ""start_time"": ""2017-11-01T12:00:00Z"",
                ""end_time"": ""2017-11-07T12:00:00Z"",
                ""timestep"": 60,
                ""fields"": [
                        {""name"": ""temp"", ""units"": ""F""},
                        {""name"": ""feels_like"", ""units"": ""F""},
                        {""name"": ""dewpoint"", ""units"": ""F""},
                        {""name"": ""wind_speed"", ""units"": ""mph""},
                        {""name"": ""wind_gust"", ""units"": ""mph""},
                        {""name"": ""baro_pressure"", ""units"": ""inHg""},
                        {""name"": ""visibility"", ""units"": ""km""},
                        {""name"": ""precipitation"", ""units"": ""in/hr""},
                        {""name"": ""cloud_cover"", ""units"": ""%""},
                        {""name"": ""cloud_ceiling"", ""units"": ""ft""},
                        {""name"": ""cloud_base"",""units"": ""ft""},
                        {""name"": ""humidity"", ""units"": ""%""},
                        {""name"": ""wind_direction"", ""units"": ""degrees""},
                        {""name"": ""precipitation_type""},
                        {""name"": ""sunrise""},
                        {""name"": ""sunset""}
                    ]
    }",
	webdata = Web.Contents(url, [Headers=headers,Content = Text.ToBinary(content)]),
    response = Json.Document(webdata),
    Source = response

in 
    response,
    
	AStepName = Table.AddColumn(#"Changed Type0", "APICall", each apiLoadSheetFunction(_))
	
in
	
	AStepName

I've just added 2 stepnames and change the order a bit.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries