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.
Hi Anonymous
Have you tried to alter your code for invoking the function to
Source = (app_id as text) => let
?
So I had replied earlier with the same question. I wanted to loop, not by adding a column but by creating a logical loop. I punted and did the custom column. Below is a snippet, it works. All of the code above the snippet is about the table from which I'm building my custom URL's (which happens to be another REST JSON call). Both the PlanID and ProjectID fields are defined as "text". They come in from the REST call orginally as "any" but I do a transform to "text" which I believe is unnecessary for this to work. Then it just expands the column which is a REST JSON call and it does the rest. I didn't "write" any of this. All I had to do was take my custom column with the URL in it then expand it. You can see where I'm getting my variable information from other columns in the table and building my URL from that. Painless.
#"Added Custom" = Table.AddColumn(#"Renamed ID to PlanID", "PlanURL", each "https://api.teamdynamix.com/TDWebApi/api/projects/"&[ProjectID]&"/plans/"&[PlanID]),
PlanURL1 = #"Added Custom"[PlanURL],
#"Converted to Table1" = Table.FromList(PlanURL1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "URL"}}),
I was able to create a custom column to contruct a basic URL, but my URL has authorization key (advanced URL). How do I add key into the the URL column? This is what I have:
#"Added Custom" = Table.AddColumn(#"Changed Type", "URL", each "https://api.appannie.com/v1.2/intelligence/apps/ios/app/"&[aa_app_id]&"/history?countries=US&feeds=downloads&device=all&start_date=2016-01-01&end_date=2017-02-28&granularity=monthly&device=iphone"&Headers.Add("Authorization",String.Format("bearer <key>")))
The bigger question is, does this custom function send requests (once invoked) using constructed URLs (not just selecting one URL at a time) and generate one big table that has contains all the responses, not just one response per URL? Just want to run this function once and get all the data.
Thanks in advance.
So in an earlier JSON call I get my bearer token. I then concatenate "Bearer " with the returned token from that earlier REST call and use that concatenated string as my bearer token. Here is the call using the bearer token...
let
BearerToken = "Bearer "&{AuthBearerToken}{0}{0},
Source = Json.Document(Web.Contents(SomeURLorURLVariable, [Headers=[Authorization=BearerToken, ContentType="application/json"]])),- Is it the same key for every url? Just connect to one url (in a new query) using the web connector. Once this works, just turn the generated query into a function as we've discussed and it will reproduce the connection but for every URL in your list. Then you will use the "expand" button to merge the results of all the returned tables into one big table
I can help with adding a bearer token parameter but it will require custom M code so I will need to get back to you I did create a custom function where appID is defined as Text and it's coming from Parameters, which I don't want. How do I get it to accept appIDs from another query( aa_app_id query)? When I changed the 'aa_app_id as table', a dropdown list allowing another query to be chosen. Does this mean that aa_app_id is searched and processed if it's found in another query? But error after invoking:
An error occurred in the ‘new_app_id’ query. Expression.Error: We cannot apply operator & to types Text and Table.
Details:
Operator=&
Left=https://api.appannie.com/v1.2/intelligence/apps/ios/app/
Right=TableMy aa_app_id query:
let
Source = Excel.Workbook(File.Contents("<filename>.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"aa_app_id", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "URL request", each "https://api.appannie.com/v1.2/intelligence/apps/ios/app/"&[aa_app_id]&"/history?countries=US&feeds=downloads&device=all&start_date=2016-01-01&end_date=2017-02-28&granularity=monthly&device=iphone"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"URL request", type text}})
in
#"Changed Type1"Thanks in advance.
I did create a custom function, but that takes appIDs from Parameters. How do I change it to take appIDs from another query? Changing aa_appid as table didn't create errors. Thanks in advance.
let
Source = (aa_app_id as text) => let
Source = Json.Document(Web.Contents("https://api.appannie.com/v1.2/intelligence/apps/ios/app/" & aa_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="bearer <key>"]])),My aa_app_id Query:
let
Source = Excel.Workbook(File.Contents("C:\Users\310234735\OneDrive - Philips Lighting\AppResearch\Appannie\AA_app_ids.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"aa_app_id", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "URL request", each "https://api.appannie.com/v1.2/intelligence/apps/ios/app/"&[aa_app_id]&"/history?countries=US&feeds=downloads&device=all&start_date=2016-01-01&end_date=2017-02-28&granularity=monthly&device=iphone"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"URL request", type text}})
in
#"Changed Type1"I did create a custom function, but that takes appIDs from Parameters. How do I change it to take appIDs from another query? Changing aa_appid as table didn't create errors. Thanks in advance.
let
Source = (aa_app_id as text) => let
Source = Json.Document(Web.Contents("https://api.appannie.com/v1.2/intelligence/apps/ios/app/" & aa_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="bearer <key>"]])),My aa_app_id Query:
let
Source = Excel.Workbook(File.Contents("C:\Users\310234735\OneDrive - Philips Lighting\AppResearch\Appannie\AA_app_ids.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"aa_app_id", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "URL request", each "https://api.appannie.com/v1.2/intelligence/apps/ios/app/"&[aa_app_id]&"/history?countries=US&feeds=downloads&device=all&start_date=2016-01-01&end_date=2017-02-28&granularity=monthly&device=iphone"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"URL request", type text}})
in
#"Changed Type1"I did create a custom function, but that takes appIDs from Parameters. How do I change it to take appIDs from another query? Changing aa_appid as table generated errors. Thanks in advance.
let
Source = (aa_app_id as text) => let
Source = Json.Document(Web.Contents("https://api.appannie.com/v1.2/intelligence/apps/ios/app/" & aa_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="bearer <key>"]])),My aa_app_id Query:
let
Source = Excel.Workbook(File.Contents("<filename>.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"aa_app_id", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "URL request", each "https://api.appannie.com/v1.2/intelligence/apps/ios/app/"&[aa_app_id]&"/history?countries=US&feeds=downloads&device=all&start_date=2016-01-01&end_date=2017-02 28&granularity=monthly&device=iphone"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"URL request", type text}})
in
#"Changed Type1"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 I got it to work. I was confused about Invoking custom function at first. It turns out I need to Invoking Custom Function within the app_id query that have all the appIDs, not invoking within the custom function itself.
Thank you all for the tips and guidance regarding this issue. I've learned a lot!!
Sorry I wasn't able to better explain it earlier! Glad you got it to work.