Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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??
Solved! Go to Solution.
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
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
Any kind of help would really be appreciated !
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:
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:
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.
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
@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?
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |