Power Query: >90 Queries?

[DerekK]

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

 

[gazpage]

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.

 

[DerekK]

HIGH FIVE

Sincerest thanks, gazpage! That very much works!