Forum Discussion
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 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.
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.
35 Replies
Quick question. Do you need to get the access token from the web call or do you already have it?
Okay, the reason I asked is because the answer to your question would change if you need to dynamically obtain the token (i.e. OAuth2).
Second question is, do you know how many url's you need to loop through or does it need to be dynamic?
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"Oh. And my bearer token is also coming from an earlier query
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?
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
