Power Query: >90 Queries?

DerekK

Board Regular
Joined
Jun 18, 2007
Messages
84
Good day, Excelsiors,

I am using Power Query to run an API call to our service provider, in order to pull through our platform users and their attendant data. The idea is that we would like to keep a "real time" register, rather than keeping a separate, manually updated register which is susceptible to human error.

The problem is, the service provider has throttled the maximum number of users per API call to 50; whereas we have >4.5K users. (The 50 limit seems to be set in stone but I can do page 1, page 2, &c.)

I was hoping someone could help me:

1. Is there anything stopping me from doing this as >90 queries, then merging them into one?

2. Is there a more efficient way to go about this? Or is there some trick I'm missing?

Hope you can help!

Sincerely,



Derek
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
That is quite a painful API...

This isn't too hard, definitely don't make 90 queries. My steps would probably be:

1. Really check that I can't push this to an API call, some APIs allow you to do things like .../UK+FR...
2. Assuming that didn't work, and assuming your API call is like this. www.[].com/page1, www.[].com/page2, then do the rest of these steps
3. Create a query that returns the data for a single page in the exact format you want. Call this query Example.
4. Go to Manage Parameters, New Parameter and create a new parameter. Name parPageNum, Type integer, default value 1.
5. Go to the Example Query and to the advanced editor. Fine the part of the code that says
www.[].com/page1
, and replace it with
www.[].com/page & parPageNum.
6. Exit the Advanced Editor and you should see no change to your table as you effectively replace 1 with 1.
7. On the queries window on the left, right click the Example Query and choose Create Function, call the function fnCallAPI
8. Next you need a fresh query that has the list of all the page numbers. If it's really as simple as my example then you could create a list of numbers from 1 to 50 by typing ={1..50} in a blank query and then converting it to a table using the toolbar.
9. Go to add column, invoke custom function. Use your function and your created column as the parameter.
10. Expand the column using the little arrows in the heading.
11. Delete helper columns, rename and set data type as needed.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,588
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top