Forum Discussion
Can I use data from a previous API request to make new requests?
I currently use the JIRA Content Pack to integrate with Atlassian JIRA and I'm trying to reuse the queries to manually access Trello's API. The trouble is, Trello has multiple requests required to get all the data I need from my account as opposed to te one request that can be made to JIRA's API. Here's what I have so far:
TrelloURL:
https://api.trello.com
FetchPage:
= (url as text, pageSize as number, skipRows as number) as table =>
let
//Here is where you run the code that will return a single page
contents = Web.Contents(TrelloURL&"/1/members/me/boards",[Query = [key = "", token = """, maxResults = Text.From(pageSize), startAt = Text.From(skipRows), fields = {"shortLink"}]]),
json = Json.Document(contents),
Value = json,
table = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
table meta [skipRows = skipRows + pageSize, total = 500]
FetchPages:
= (url as text, pageSize as number) =>
let
Source = GenerateByPage(
(previous) =>
let
skipRows = if previous = null then 0 else Value.Metadata(previous)[skipRows],
totalItems = if previous = null then 0 else Value.Metadata(previous)[total],
table = if previous = null or Table.RowCount(previous) = pageSize then
FetchPage(url, pageSize, skipRows)
else null
in table,
type table [Column1])
in
Source
GenerateByPage:
= (getNextPage as function, optional tableType as type) as table =>
let
listOfPages = List.Generate(
() => getNextPage(null),
(lastPage) => lastPage <> null,
(lastPage) => getNextPage(lastPage)
),
tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
firstRow = tableOfPages{0}?,
keys = if tableType = null then Table.ColumnNames(firstRow[Column1])
else Record.FieldNames(Type.RecordFields(Type.TableRow(tableType))),
appliedType = if tableType = null then Value.Type(firstRow[Column1]) else tableType
in
if tableType = null and firstRow = null then
Table.FromRows({})
else
Value.ReplaceType(Table.ExpandTableColumn(tableOfPages, "Column1", keys), appliedType)The URL and 3 queries above, get me a table of Trello boards filtered down to an ID and the "shortlink". What I then need to do, is to then make a separate request for each "shortlink" to get all of the data from each board. The endpoint for this is:
https://api.trello.com/1/boards/"shortlink here"
My Trello account has almost 1k boards so originally I tried to get the data a different way, but the only other option times out every time on Trello's end (and it wouldn't have been quite everything I need anyway). I have been using Power BI for little while now, but I'm new to Power Query and I haven't been able to find anything on whether or not I can do this.
Any help is much appreciated, thanks!
1 Reply
- v-ljerr-msftMicrosoft Employee
Hi mithrandir,
According to your description above, I am just wondering if you can try creating a new query/table which references the table of Trello boards with ID and the "shortlink" first, then make a separate request for each row of "shortlink" to get all of the data from each board. :smileyhappy:
Reference:
https://community.powerbi.com/t5/Desktop/how-to-reference-named-table-in-a-query/td-p/17379
https://blog.crossjoin.co.uk/2016/11/20/referenced-queries-and-caching-in-power-bi-and-power-query/
Regards