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
saikishore
New Member

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

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"",},}"

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

15 REPLIES 15
akashgera
Helper I
Helper I

Hi @ImkeF@v-yuta-msft


I am using REST API to fetch the data from 3rd party using POST method, I have used the same parameters in Header, Body in postman, and it is giving me the data in JSON format, but when I am using the same thing in power query, it is giving me an error: Response Msg: Invalid APi credentials

Response code:11

response : NULL
Please have a look in my power query below and help me what is wrong in this :-

let
ApiOwner= "XXXXXXXX",
ApiKey="XXXXXXXXXXXXXXXXXXXXXXXXX",
url = "https://dentalkart.vineretail.com/RestWS/api/eretail/v3/sku/inventoryStatus",
header= [#"Authorization" = ApiKey & ApiOwner,
#"Content-Type" = "application/x-www-form-urlencoded"],

RequestBody=
"{
""skuCodes"":""[]"",
""fromDate"":""18/06/2021 15:40:30"",
""toDate"":""23/06/2021 15:40:30"",
""pageNumber"":""1"",
""dropShipFlag"":""no"",
""locCode"":""
}",

webdata = Web.Contents(url, [Content = Text.ToBinary(RequestBody), Headers=header]),
response = Json.Document(webdata)

in
response

 

 

akashgera_0-1626959494830.png

 


Any kind of help would really be appreciated !

 

ImkeF
Super User
Super User

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"",},}"

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

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

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)?

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

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. 

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 

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

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.

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!

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.

 

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

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

 

 

No, conversion to string is not done automatically. Instead you have to write it like so:

 

content = "{
        ""geocode"": {
                    ""lon"": " & Text.From(_[value1]) & ",
                    ""lat"": 46.4498901367188

 

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

Anonymous
Not applicable

@ImkeF Hi guys for both of you a big thanks. This query helped me a lot. Just i have an additional question to ask, Whenever i execute my API call with this same Query, i get an error 

The requested resource does not support http method 'POST'.

 

Can you please help me on this?

Anonymous
Not applicable

I could actually resolve it because it was my mistake for "GET" I was using the same request query. I changed accordingly and it works. Can you guys help me in how do I map one of the column values for each results ?

Right now the results returned are of for first row of record from table. What i want is to return results for all tabluar data: @ericOnline @ImkeF The following fetches only single/first record. I want to fetch it for all the records

APICall = AStepName{0}[APICall]

Hi @Anonymous 

you need to add a column to your table where you perform the Web API call. Please check out this article: https://www.thebiccountant.com/2018/03/22/web-scraping-2-scrape-multiple-pages-power-bi-power-query/

 

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

v-yuta-msft
Community Support
Community Support

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

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.