When you add a custom column (Invoke Custom Function), change the drop down to Column Name and select the app_id column. Then, the function will pass in the app_id value from each row into the function (into the URL) and perform the query steps you designed earlier. If you need to pass a bearer token to the URL for each app_id value, if it is the same bearer token for every app_id, you can add an Authorization header to the URL request.
For example, I have a dashboard that pulls data from Twitter. Once I obtain the access token (it is the result of a different query named AccessToken) here is the function I use to query Twitter:
(params) =>
let
GetJsonQuery = Web.Contents("https://api.twitter.com/1.1/search/tweets.json" & params,
[
Headers = [#"Authorization"=AccessToken]
]
),
FormatAsJsonQuery = Json.Document(GetJsonQuery),
data = try FormatAsJsonQuery[statuses] otherwise null,
next = try FormatAsJsonQuery[search_metadata][next_results] otherwise null,
res = [Data=data, Next=next]
in
resI have a table with a column of search words, where the column name is "keyword". The parameter named "params" that started off the function above is defined as:
params = "?q=" & keyword & "&count=100"
So you can see how the column of hard coded search terms is passed into the url part defined by "params" which is in turn passed into the function. The function runs on every row in my table of "keyword", so each row returns a table of results, which I can then expand and Power BI will automatically append the results into one table.
Note: This is not the entire query and will not return results (the "data", "next" and "res" variables are used in another query that iterates through pages of results). Hopefully this shows you how to structure your web call using the bearer token.
