Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Advocate II
Advocate II

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

Anonymous
Not applicable

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?

Try with Text.From( [Counter] )

Anonymous
Not applicable

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?

@Anonymous Can you post your M Code?

I have the same problem in my project.

 

Thanks,

 

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

 

Anonymous
Not applicable

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

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

 

 

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.

 

2017-06-26_21h11_08.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors