Forum Discussion
JSON query based on another query
- 9 years ago
Sorry I ommitted a "let". See below:
let function = (ID as text) => let Source = Json.Document(Web.Contents("https://api.provider.com/survey_results?survey_id=" & ID, [Headers=[Authorization="Bearer ACCESS_TOKEN", Allow="application/json", #"NGIN-API-VERSION"="0.1"]])) in Source in function
Hi,
You're correct. The ID field is a column of IDs in the first table. I'd love to hear how to accomplish this. Thanks.
A good general overview is here:
http://radacad.com/custom-functions-made-easy-in-power-bi-desktop
I usually follow the pattern of just changing my M code to indicate it is function and passing in a value. For example, change your M code for the second query to the following:
let function = (ID as text) =>
Source = Json.Document(Web.Contents("https://api.provider.com/survey_results?survey_id=" & ID,
[Headers=[Authorization="Bearer ACCESS_TOKEN", Allow="application/json", #"NGIN-API-VERSION"="0.1"]]))
in
Source
in
functionThen, in your original query/table with the list of IDs, go to Add Column > Invoke Custom Function, choose the function you just made as the function query, and change the table drop down from text to column name and select the [ID] column.
This should then run your API call for every line in your table, plugging in the ID field for each row into the function and return the result. Note, you will have to expand the result column by clicking on the expand icon in header of the column.
- adam_cogswell9 years agoRegular Visitor
Hi,
Thanks for the reference information, and the M code. I was trying to use your code just to get more familiar with the language, but it's throwing a "Token EoF expected." error when I try to save it. Seems to dislike the two "in" statements at the end:
let function = (ID as text) => Source = Json.Document(Web.Contents("https://api.provider.com/survey_results?survey_id=" & ID, [Headers=[Authorization="Bearer ACCESS_TOKEN", Allow="application/json", #"NGIN-API-VERSION"="0.1"]])) in function in Source- dkay84_PowerBI9 years agoMicrosoft Employee
You switched the two "in" statements compared to my example
- adam_cogswell9 years agoRegular Visitor
Sorry, I had re-arranged them trying to troubleshoot it. It doesn't work either way...