Solved: script running looping URLs and get data

Anonymous · ‎03-02-2017

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 looping process is ideal. Does anyone know how this can be done and processed in Power BI (sample script is preferred)? Once I have this script, what are the steps to get Power BI process the script?

URLs: - https://inteligence/apps/1/...

https://inteligence/apps/2/..

https://inteligence/apps/3/...

...

with Authorization - bearer <key>

Thanks in advance.

dkay84_PowerBI · ‎03-08-2017

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.

View solution in original post

Anonymous · ‎07-26-2019

Hi Guys,

I was wondering if you might be able to help. Im trying to do something similar to what you have described above but querying TFS on prem.

By using the filters on the TFS api ive managed to get back a list of all the work items that are relative to a particular project and thier individual URL. Is there a way to make a query similar to what you've done above and build a function that iterates through the rows.

Effectively it would make a get request for each item in the master table of work items

the work items list looks like this

im not very capable with M - the api requests were orignally all windows authenticated

dickfederle · ‎03-02-2017

This is EXACTLY my question. I have the query below where I'm looking at the first row of the URL I want (I created it from another web.content call that I then did a calculated column on to format the URL.) I just need to figure out how to "loop" through each row of the table to get all of my individual data sources.

Here is the query. The stuff in green is what does all of REST/Json call. The stuff in red is my constructed URL from another REST/JSON call that I calculated. Basically it's a list that I want to iterate through to end up with 1 query that is accessing multiple "data sets" all from the same REST query with different parameter. You can ignore all the remainder. That's converting the returns into tables and then expanding the columns of the JSON.

I must say that 95% of this was all generated by Power BI which I have to admit is way cool.

let
    BearerToken = "Bearer "&{AuthBearerToken}{0}{0},
    Source = Json.Document(Web.Contents({PlanURL}{0}{0}, [Headers=[Authorization=BearerToken, ContentType="application/json"]])),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{11}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"OutlineNumber", "Wbs", "IsParent", "IndentLevel", "ParentID", "PlanID", "PlanName", "IsFlagged", "TicketID", "TicketAppID", "Field1", "Field2", "Field3", "Field4", "Field5", "Field6", "Field7", "Field8", "Field9", "Field10", "IsMilestone", "IsConvertedFromTicket", "HasExternalRelationships", "IsExternalRelationshipViolated", "CanShiftForward", "ShiftForwardDate", "HasIssues", "HasAttachments", "Priority", "IsStory", "OpenIssuesCount", "IssuesCount", "Predecessors", "PredecessorsOutlineNumbersComplex", "Resources", "ResourcesNamesAndPercents", "IsCriticalPath", "StatusID", "Status", "OrderInParent", "ID", "Title", "Description", "StartDateUtc", "EndDateUtc", "Duration", "DurationString", "CompletedDateUtc", "EstimatedHoursAtCompletion", "ProjectID", "ProjectIDEncrypted", "ProjectName", "CreatedUID", "CreatedFullName", "CreatedDate", "EstimatedHours", "EstimatedHoursBaseline", "ActualHours", "PercentComplete", "StartDateBaselineUtc", "EndDateBaselineUtc", "StoryPoints", "ValuePoints", "RemainingHours", "PlanType", "VarianceDays"}, {"Column1.OutlineNumber", "Column1.Wbs", "Column1.IsParent", "Column1.IndentLevel", "Column1.ParentID", "Column1.PlanID", "Column1.PlanName", "Column1.IsFlagged", "Column1.TicketID", "Column1.TicketAppID", "Column1.Field1", "Column1.Field2", "Column1.Field3", "Column1.Field4", "Column1.Field5", "Column1.Field6", "Column1.Field7", "Column1.Field8", "Column1.Field9", "Column1.Field10", "Column1.IsMilestone", "Column1.IsConvertedFromTicket", "Column1.HasExternalRelationships", "Column1.IsExternalRelationshipViolated", "Column1.CanShiftForward", "Column1.ShiftForwardDate", "Column1.HasIssues", "Column1.HasAttachments", "Column1.Priority", "Column1.IsStory", "Column1.OpenIssuesCount", "Column1.IssuesCount", "Column1.Predecessors", "Column1.PredecessorsOutlineNumbersComplex", "Column1.Resources", "Column1.ResourcesNamesAndPercents", "Column1.IsCriticalPath", "Column1.StatusID", "Column1.Status", "Column1.OrderInParent", "Column1.ID", "Column1.Title", "Column1.Description", "Column1.StartDateUtc", "Column1.EndDateUtc", "Column1.Duration", "Column1.DurationString", "Column1.CompletedDateUtc", "Column1.EstimatedHoursAtCompletion", "Column1.ProjectID", "Column1.ProjectIDEncrypted", "Column1.ProjectName", "Column1.CreatedUID", "Column1.CreatedFullName", "Column1.CreatedDate", "Column1.EstimatedHours", "Column1.EstimatedHoursBaseline", "Column1.ActualHours", "Column1.PercentComplete", "Column1.StartDateBaselineUtc", "Column1.EndDateBaselineUtc", "Column1.StoryPoints", "Column1.ValuePoints", "Column1.RemainingHours", "Column1.PlanType", "Column1.VarianceDays"}),
    #"Expanded Column1.Predecessors" = Table.ExpandListColumn(#"Expanded Column1", "Column1.Predecessors"),
    #"Expanded Column1.Predecessors1" = Table.ExpandRecordColumn(#"Expanded Column1.Predecessors", "Column1.Predecessors", {"PredTaskID", "DepTaskID", "Lag", "RelationshipType"}, {"Column1.Predecessors.PredTaskID", "Column1.Predecessors.DepTaskID", "Column1.Predecessors.Lag", "Column1.Predecessors.RelationshipType"}),
    #"Expanded Column1.Resources" = Table.ExpandListColumn(#"Expanded Column1.Predecessors1", "Column1.Resources"),
    #"Expanded Column1.Resources1" = Table.ExpandRecordColumn(#"Expanded Column1.Resources", "Column1.Resources", {"ResourceUID", "ResourceFullName", "PercentAssignedWhole"}, {"Column1.Resources.ResourceUID", "Column1.Resources.ResourceFullName", "Column1.Resources.PercentAssignedWhole"})
in
    #"Expanded Column1.Resources1"

dickfederle · ‎03-02-2017

Oh. And my bearer token is also coming from an earlier query

dkay84_PowerBI · ‎03-03-2017

Last question: Is the paging represented by a number at the end of the url?

What I mean is are all the url's the same except for the end is something like "page=1", "page=2" etc?

Or is it cursor based pagination?

Anonymous · ‎03-03-2017

The only part that changes is the middle part to identify each app, with unique random number (e.g., https://...3/...). Thanks.

dkay84_PowerBI · ‎03-03-2017

Can you give a full example of what the URL looks like?

Essentially, what I am trying to understand is the strucuture of the URL so that we can figure out the best approach. Ultimately, what we will end up doing is turning your query into a function and invoking that function on a table of your unique URLs, but how we get to that table is the wild card.

Anonymous · ‎03-03-2017

Here they are:

https://api.appannie.com/v1.2/intelligence/apps/ios/app/310738695/history?countries=US&feeds=downloads&granularity=monthly&device=all&start_date=2013-09-01&end_date=2017-02-28

https://api.appannie.com/v1.2/intelligence/apps/ios/app/215034499/history?countries=US&feeds=downloads&granularity=monthly&device=all&start_date=2013-09-01&end_date=2017-02-28

with Header, Authorization bearer <key> Hope this clarifies things. Thanks.

dkay84_PowerBI · ‎03-03-2017

Yes thank you. And you said that the number in the middle (bold text) is randomly generated, meaning we can't have a list of these in advance? If so, how do you get the URL in the first place? Is there an earlier query that gets this information?

Anonymous · ‎03-03-2017

THe list of these URLs will be generated in advace (most likely XLS), then this looping script will do process the URLs and get responses back. Thanks.

Anonymous · ‎03-06-2017

@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 · ‎03-06-2017

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.

Yeshde · ‎06-04-2023

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

Anonymous · ‎03-07-2017

@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 · ‎03-07-2017

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 · ‎03-07-2017

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.

Anonymous · ‎03-07-2017

Hi @Anonymous

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

Source = (app_id as text) => let

?

dkay84_PowerBI · ‎03-08-2017

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

dickfederle · ‎03-08-2017

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

Anonymous · ‎03-08-2017

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=d... <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.

dkay84_PowerBI · ‎03-08-2017

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

script running looping URLs and get data

Helpful resources

Europe’s largest Microsoft Fabric Community Conference

Power BI Monthly Update - August 2024

Fabric Community Update - August 2024

How to Get Your Question Answered Quickly

European Microsoft Fabric Community Conference

script running looping URLs and get data

Helpful resources

Europe’s largest Microsoft Fabric Community Conference

Power BI Monthly Update - August 2024

Fabric Community Update - August 2024

How to Get Your Question Answered Quickly