Using API as source, can't get all data

[Yaso]

Hey guys,

Aim – Use Freshdesk’s API to build some reports.  Skill level – Rookie

I got to a point where I can load the data, convert to table etc but when I click on a visual, it was showing only 30 items. 

Researched and found out about pagination. So I updated my source to: https://domain.freshdesk.com/api/v2/tickets?per_page=100. But it’s showing only 100 tickets now. Whilst I want all tickets.

Did a bit more digging on https://developers.freshdesk.com/api/#list_all_tickets, says for all tickets use this handle: /api/v2/tickets?updated_since=2015-01-19T02:00:00Z

And when I do, I’m back at 30 tickets on a page.

I feel like a vulture circling around the prize, with a blindfold. Can someone see where I’m going wrong?

Thank you for your time and brainspace, 

Yaso 

[Seth_C_Bauer · Accepted solution]

@Yaso First, I just have to mention your post is great. Clear, concise and funny to boot.

The API call returns 30 records by default and 100 by extending the page. But the confusion is in that it will never just give you a full output of your data. You have to work for it 🙂 As an example on the Freshdesk site here #3 you need to call the "other" pages. If you have 1,000 tickets, you would need to make 10 seperate API calls to retrieve the complete list.

In you "per_page=100" example, the second call would end with "per_page=100&page=2"

This same issue is answered here in this technet forum by @ImkeF who is a genious with Power Query and M. And while that post is specific to Power Query of old, the same PQ engine is used in Power BI.

Another example of a solution would start out from this thread

So, the bad news, is there is a bit to figure out yet in order to dynamically generate the entire list, but the good news is that there is a way to do it and you are headed in the right direction.

[Yaso]

@Seth_C_Bauer and @ImkeF thank you, you two. I was thrilled to find the replies - you quite frankly made my morning.

So I'm still on my learning path - lots of googling and researching. The learning curve will be a steep one as I know not of M, but I'll try. Learning resources will be appreciated too. I found a 100+ pager  so will be keen to look at your recommendations. 

@Seth_C_Bauer, re "This same issue is answered here in this technet forum by @ImkeF who is a genious with Power Query and M. And while that post is specific to Power Query of old, the same PQ engine is used in Power BI" 

Can you please pass me the link?  

[Yaso]

Update: 

Per page=100. So I created 10 separate tables which got me 1000 tickets, page 1 to page 10 (10 pages is enough for now to test the application/my learning)

Then I appended them all. I'm not sure if this is what you meant, @Seth_C_Bauer when you said create 'multiple API calls' 

I didn't do much M. 

Seems to have worked 🙂 Huzzah

[Seth_C_Bauer]

@Yaso Glad to hear you are on track, I've found some better blogs about the topic, so ignore the first reference and missing link (sorry). My comment relates to the fact that you can create an M query that will automatically call the api multiple times. I believe this outputs into "x" number of rows with "Values" for the outputed pages. You can that transform that initial list into a table that contains all the records.

Static Process (where you would have to input the number of pages)

Dynamic Process (This would just pull back all the records.) This process is an example using Power BI, so while it may take a bit to figure out how to replace your API calls, in the long run it is probably the solution you want to invest time into.

Both explain all the details of what is going on, so they are both good reads. I'd throw some code at you if I had it, but I haven't had to do this myself yet 🙂

[ImkeF]

Hi @Seth_C_Bauer thanks for the flowers!

Check out this:  https://community.powerbi.com/t5/Desktop/how-to-create-a-query-that-paginates/m-p/163788#M71392 there you'll find some codes for pagination that might work in your case.