Forum Discussion
script running looping URLs and get data
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.
dkay84_PowerBI Thanks for the details. I followed the steps and created the function (func_app_id). When I invoke the function that connects to list of URLs (appID, query from an XLS), the following error appears and how to successfully invoke the function?:
An error occurred in the ‘Func_app_id’ query. Expression.Error: We cannot convert the value "app_id" to type Record.
Details:
Value=app_id
Type=Type
--------------------------------------------
ALL QUERIES SO FAR
1. Query with one URL for function to be created later
let
Source = Json.Document(Web.Contents("https://api.appannie.com/v1.2/intelligence/apps/ios/app/" & app_id & "/history?countries=US&feeds=downloads&device=all&start_date=2016-01-01&end_date=2017-02-28&granularity=monthly&device=iphone", [Headers=[Authorization="<key>"]])),
#"Converted to Table" = Record.ToTable(Source),
#"Removed Top Rows" = Table.Skip(#"Converted to Table",1),
#"Removed Rows" = Table.RemoveRows(#"Removed Top Rows",1),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Rows",7),
#"Removed Rows1" = Table.RemoveRows(#"Removed Bottom Rows",1),
#"Removed Rows2" = Table.RemoveRows(#"Removed Rows1",2,5),
#"Removed Rows3" = Table.RemoveRows(#"Removed Rows2",3),
#"Transposed Table" = Table.Transpose(#"Removed Rows3"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Expanded list" = Table.ExpandListColumn(#"Promoted Headers", "list"),
#"Expanded list1" = Table.ExpandRecordColumn(#"Expanded list", "list", {"device", "feed", "estimate", "date"}, {"list.device", "list.feed", "list.estimate", "list.date"})
in
#"Expanded list1"
2. Query with URLs as table (appID) for function to invoke later
let
Source = Excel.Workbook(File.Contents("...AA_app_ids.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"aa_app_id", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "app_id", each [aa_app_id]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"aa_app_id", type text}, {"app_id", type text}})
in
#"Changed Type1"
3. Parameter (app_id)
"891941989" meta [IsParameterQuery=true, List={"660944635", "891941989"}, DefaultValue="660944635", Type="Text", IsParameterQueryRequired=true]
4. Function created (Func_app_id)
let
Source = (app_id as table) => let
Source = "app_id",
#"Converted to Table" = Record.ToTable(Source),
#"Removed Top Rows" = Table.Skip(#"Converted to Table",1),
#"Removed Rows" = Table.RemoveRows(#"Removed Top Rows",1),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Rows",7),
#"Removed Rows1" = Table.RemoveRows(#"Removed Bottom Rows",1),
#"Removed Rows2" = Table.RemoveRows(#"Removed Rows1",2,5),
#"Removed Rows3" = Table.RemoveRows(#"Removed Rows2",3),
#"Transposed Table" = Table.Transpose(#"Removed Rows3"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Expanded list" = Table.ExpandListColumn(#"Promoted Headers", "list"),
#"Expanded list1" = Table.ExpandRecordColumn(#"Expanded list", "list", {"device", "feed", "estimate", "date"}, {"list.device", "list.feed", "list.estimate", "list.date"})
in
#"Expanded list1"
in
Source
Thanks in advance.