cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cousinlarry Regular Visitor
Regular Visitor

JSON API - How to do pagination?

I am looking to get all the tasks from Teamwork API http://developer.teamwork.com/todolistitems#retrieve_all_task but I can only get 250 at a time - I need to use pagination to get the rest. 

 

How can I build a PowerQuery that can use https://company.teamwork.com/tasks.json?page=2 (and 3,4 etc?) up to a predefined value, say 20 pages? 

 

 

 

 

10 REPLIES 10
IvanBond Frequent Visitor
Frequent Visitor

Re: JSON API - How to do pagination?

Hello,

 

I did a pagination for SAP Business ByDesign web services and described process in my blog:

https://bondarenkoivan.wordpress.com/2015/10/23/query-sap-business-bydesign-web-services-via-power-q...

 

In the end of the post you may find solution with usage of List.Generate.

This should help you.

 

BR,

Ivan

cousinlarry Regular Visitor
Regular Visitor

Re: JSON API - How to do pagination?

Here's some code:

 

let
    Source = 
        List.Generate(
            ()=>[Counter=0],
            each [Counter]<10000,
            each [Counter=[Counter]+1000],
            each Json.Document(Web.Contents("https://xyz.com/rest/v10/Opportunities?OAuth-Token=2342423&max_num=1000&offset=" & [Counter]))
        ),      
    Source1 = Source{0}
in
    Source1

However I get the error 

Expression.Error: We cannot apply operator & to types Text and Number.

 

I have tried VALUE and FORMAT to convert [Counter] to a string - can anyone point me in the right direction?

IvanBond Frequent Visitor
Frequent Visitor

Re: JSON API - How to do pagination?

Try with Text.From( [Counter] )

cousinlarry Regular Visitor
Regular Visitor

Re: JSON API - How to do pagination?

Thanks - that worked. Wonder why I couldn't find that in documentation....

 

I am still only getting 1000 results, I guess I am not appending to the Source object- any clues on how to append each request for 1000 results so I get 10000 records in the Source object?

IvanBond Frequent Visitor
Frequent Visitor

Re: JSON API - How to do pagination?

Hi,

 

In the end you take Source{0} - first element of Source list. Maybe this is an issue?

 

In my case I had to add Delay between steps of loop

Page = Function.InvokeAfter(()=>GetPage(Accounts_List_To_Query), #duration(0,0,0, Delay))

 

because web service had been sending me the same response, probably due to very frequent queries.

BR,

Ivan

 

cousinlarry Regular Visitor
Regular Visitor

Re: JSON API - How to do pagination?

Thanks Ivan, I'll check it out. Any chance of posting your entire code snippet for paginated APIs?

IvanBond Frequent Visitor
Frequent Visitor

Re: JSON API - How to do pagination?

You are welcome!

 

Here is my code for paging with delay between queries

 

let
	Accounts = Table.ToList(Excel.CurrentWorkbook(){[Name=”ACCOUNTS”]}[Content]),
	Count = List.Count(Accounts),
	Step = 500,
	Steps = Number.RoundUp(Count / Step),
	Delay = 1, // seconds
	r = List.Buffer(
		List.Skip(List.Generate(
			() => [
				i = 0,
				Page = null
				],
			each [i] <= Steps,
			each let
				Accounts_List_To_Query = List.Range(Accounts, Step * [i], Step),
				GetPage = (Accs as list) => GetListOfAccountsGeneralData(Accs)
			in [
				i = [i] + 1,
				Page = Function.InvokeAfter(()=>GetPage(Accounts_List_To_Query), #duration(0,0,0, Delay))
				],
			each [Page]
		)
		)),
	#”Table from List” = Table.FromList(r, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
	#”Expand Column1″ = Table.ExpandTableColumn(#”Table from List”, “Column1”, {“Table”}, {“Table”}),
	#”Expand Table” = Table.ExpandTableColumn(#”Expand Column1″, “Table”, {“InternalID”, “Relationship”}, {“InternalID”, “Relationship”}),
	#”Expanded Relationship” = Table.ExpandTableColumn(#”Expand Table”, “Relationship”, {“RelationshipBusinessPartnerInternalID”, “RoleCode”}, {“RelationshipBusinessPartnerInternalID”, “RoleCode”})

in
	#”Expanded Relationship”

It was published as part of post on my blog. In the bottom of post you may find link to Excel workbook, where queries are included.

 

Best regards,

Ivan

 

 

gopichilla Member
Member

Re: JSON API - How to do pagination?

@cousinlarry Can you post your M Code?

I have the same problem in my project.

 

Thanks,

 

gopichilla Member
Member

Re: JSON API - How to do pagination?

I am using this M code

 

let Iterations = 10, 
BaseURL = "https://company.teamwork.com/tasks.json?page=", //call this function recursively 
GetTaskPage = (page) => let TaskSource = Json.Document(Web.Contents(BaseURL & Text.From(page))), 
Tasks = try TaskSource[#"todo-items"] otherwise null in Tasks, //delay is set to 0 seconds here, though Teamwork will throttle you if you pound on it. Recommend setting to 1 second 
AllTasks = List.Generate( () => [i=1, response = GetTaskPage(i)], each [i]<=Iterations and [response]<>null, each [i=[i]+1, response = Function.InvokeAfter(()=>GetTaskPage(i), #duration(0,0,0,0))]),
#"Converted to Table" = Table.FromList(AllTasks, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"response"}, {"Column1.response"}), 
#"Expanded Column1.response" = Table.ExpandListColumn(#"Expanded Column1", "Column1.response"), 
#"Expanded Column1.response1" = Table.ExpandRecordColumn(#"Expanded Column1.response", "Column1.response", {"id", "canComplete", "comments-count", "description", "has-reminders", 
"has-unread-comments", "private", "content", "order", "project-id", "project-name", "todo-list-id", "todo-list-name", "tasklist-private", "tasklist-isTemplate", "status", 
"company-name", "company-id", "creator-id", "creator-firstname", "creator-lastname", "completed", "start-date", "due-date-base", "due-date", "created-on", "last-changed-on", 
"position", "estimated-minutes", "priority", "progress", "harvest-enabled", "parentTaskId", "lockdownId", "tasklist-lockdownId", "has-dependencies", "has-predecessors", "hasTickets", 
"timeIsLogged", "attachments-count", "responsible-party-ids", "responsible-party-id", "responsible-party-names", "responsible-party-type", "responsible-party-firstname", 
"responsible-party-lastname", "responsible-party-summary", "predecessors", "canEdit", "viewEstimatedTime", "creator-avatar-url", "canLogTime", "userFollowingComments", 
"userFollowingChanges", "DLM"}, {"id", "canComplete", "comments-count", "description", "has-reminders", "has-unread-comments", "private", "content", "order", "project-id", 
"project-name", "todo-list-id", "todo-list-name", "tasklist-private", "tasklist-isTemplate", "status", "company-name", "company-id", "creator-id", "creator-firstname", 
"creator-lastname", "completed", "start-date", "due-date-base", "due-date", "created-on", "last-changed-on", "position", "estimated-minutes", "priority", "progress", "harvest-enabled",
 "parentTaskId", "lockdownId", "tasklist-lockdownId", "has-dependencies", "has-predecessors", "hasTickets", "timeIsLogged", "attachments-count", "responsible-party-ids", 
"responsible-party-id", "responsible-party-names", "responsible-party-type", "responsible-party-firstname", "responsible-party-lastname", "responsible-party-summary", 
"predecessors", "canEdit", "viewEstimatedTime", "creator-avatar-url", "canLogTime", "userFollowingComments", "userFollowingChanges", "DLM"}),
//i had some other column manipulation here to fit my business logic needs. Omitted in this sample. //Change column data type for an inner merge join needed for my report. You'll likely be joining to the Projects API too, so this might be needed in that query too for the join 
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.response1",{{"project-id", Int64.Type}, {"id", Int64.Type}}),
    #"Expanded predecessors" = Table.ExpandListColumn(#"Changed Type", "predecessors"),
    #"Expanded predecessors1" = Table.ExpandRecordColumn(#"Expanded predecessors", "predecessors", {"id", "type", "name"}, {"id.1", "type", "name"})
in
    #"Expanded predecessors1"

But it is only possible to Tasks and I tried to Comments, Files, etc.. in Same Power BI file I am getting error like

The Column 'Column1'of the table wasn't found.