Forum Discussion

saikishore's avatar
saikishore
New Member
8 years ago
Solved

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/...
  • ImkeF's avatar
    ImkeF
    8 years ago

    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"",},}"
ImkeF's avatar
ImkeF
Community Champion
7 years ago

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
ericOnline's avatar
ericOnline
Post Patron
7 years ago

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's avatar
    ericOnline
    Post Patron
    7 years ago

    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!

    • ImkeF's avatar
      ImkeF
      Community Champion
      7 years ago

      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.

       

      • ericOnline's avatar
        ericOnline
        Post Patron
        7 years ago

        Hm. The response returns in a single column at the end of the Source table when using the syntax you provided, thank you very much.

        I'm now trying to substitute some of the values from the Source table into the API call itself.

         

        Example: From the full API call, I'm attempting to substitute the longitude from the table into the API call:

         

         

        content = "{
        ""geocode"": {
                    ""lon"": " & _[value1] & ",
                    ""lat"": 46.4498901367188

        Where "value1" is a longitude value of type Decimal Number from the Source table itself. I'm assuming the " " above turn the value1 into a string. I've tried _[value1] alone as well. Both attempts result in a Status 400 Error from the API.

         

        I'm wondering if moving the Table.AddColumn() to the end of the query is affecting this ability. 


        What are your thoughts ImkeF?