Forum Discussion

mithrandir's avatar
mithrandir
Helper I
9 years ago

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