Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

script running looping URLs and get data

Hi fellow Power BI users,   I know Power BI Desktop has 'From Web' feature to get data, but it can only process one URL at a time.  If I have multiple (but similar) URLs, I think a script running l...
Need Help
  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    9 years ago

    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
    res

    I 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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

@dkay84_PowerBI I provided more info last week and just want to see if there's any new update on creating the looping script.  Thanks in advance.

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago

If you have a list of URLs ahead of time, you can load that into Power BI as a table with URL as a column.  Then, as a separate query, connect to an individual URL using the web connector and add the appropriate headers to the M code.  Continue through the query design process (i.e. renaming columns, data types, transformations etc.).  Once completed, right click on this query and select "Convert to Function".  This will take all the steps you performed and convert to a function that can be called for every URL in the table you loaded earlier.  Once it is a function, you will need to edit the start of the code so it looks like the following:

 

myFunction = (column) as table =>

     let 

          source = column,

          

After this source step, your next step should be the web contents step, and you will replace the URL that was hard coded there with "source" (no quotes).  Go to the table of URLs and add a column > "Invoke Custom Function" and choose the function myFunction and from the other drop down select the column name of the URLs column.

 

If you need more detail on how to do what I described, let me know.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous

     

    Have you tried to alter your code for invoking the function to

     

    Source = (app_id as text) => let

    ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago
    I believe your problem is that the column of app_id from your excel file is being stored as an integer data type. You need to convert this to string so it can be concantenated with the other url parts
  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago
    Also for your function query remove the quotes from app_id
  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago
    And just to be clear, you invoke this function as a custom column on the table that pulled in the list of app ids and select the app_id column from the dropdown menu
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    1. I believe your problem is that the column of app_id from your excel file is being stored as an integer data type. You need to convert this to string so it can be concantenated with the other url parts

     

    app_id column was saved as Text, the same way as in Parameter as Text, which worked using one URL.

    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"app_id", type text}})

     

    2. Also for your function query remove the quotes from app_id

     

    Removed.

     

    3. And just to be clear, you invoke this function as a custom column on the table that pulled in the list of app ids and select the app_id column from the dropdown menu

     

    Yes, appID (query created using app_id column from XLS) is chosen from the dropdown menu and invoked. 

     

    I still see the same error.  

     

    An error occurred in the ‘Func_app_id’ query. Expression.Error: We cannot convert a value of type Table to type Record.
    Details:
    Value=Table
    Type=Type

     

    Thanks in advance.

  • dickfederle's avatar
    dickfederle
    Regular Visitor
    9 years ago

    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"}}),

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago

    Anonymous is correct.  Once you change the type to text, this should work.  I just tested this on a generic list of URLs where my function was a simple web call to get basic info:

     

    let
    Source = (url as text) => let
        Source = Web.Page(Web.Contents(url)),
        Data0 = Source{0}[Data]
    in
        Data0
in
    Source

    In my example, I have a list of complete URLs in a column, so in your function you will need to modify it (as you did earlier) to concatenate the parameter (app_id) with the rest of the URL string.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.

  • dickfederle's avatar
    dickfederle
    Regular Visitor
    9 years ago

    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"]])),

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago
    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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=&amp;
    Left=https://api.appannie.com/v1.2/intelligence/apps/ios/app/
    Right=Table

     

    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"

     

    Thanks in advance.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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"

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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"

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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"

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago

    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
    res

    I 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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!!

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago

    Sorry I wasn't able to better explain it earlier!  Glad you got it to work.

  • Yeshde's avatar
    Yeshde
    New Member
    3 years ago

    Hey dkay84_PowerBI , 

    thanks a lot for the solution, but iam facing an issue after all this. The function i created returns all the tables from the list of URLs but when i expand and click on close and apply , It throws an error called "Access to the resource is forbidden" , I tried clearing out all permissions multiple times but resulted in same thing

    Ps:I am passing my bearer token along with the url in headers

     

    Regards

    Yeswanth