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.
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?
The only part that changes is the middle part to identify each app, with unique random number (e.g., https://...3/...). Thanks.
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.