Is it possible to combine API calls with neq/pagination?

[hexitated]

Hi, I have the following case.

I need to get all of our subprojects which are around 1000. Unfortunately the API of this service is not really flexible and I have to work with offset/limit to go through all the data. On top of the I have to exclude around 4 project statuses via [neq] which means I have to make up to 4 calls (for every neq one because I didn't find a way to combine them into one call).

So it looks something like this:

https://api..net/subprojects?status[neq]=2&limit=200

https://api..net/subprojects?status[neq]=6&limit=200

https://api..net/subprojects?status[neq]=8&limit=200

https://api..net/subprojects?status[neq]=10&limit=200

But this obviously also brings chaos into my pagination because the results would differ all the time.

How would you solve this? In the end I just want all subprojects into one table that do not have status 2, 6, 8 and 10.

There has to be an easier option that making up to > 10 single requests and then combining the tables together? I Hope?

Best regards

[v-deddai1-msft · Accepted solution]

Hi @hexitated ,

Please refer to the following steps to combine your API source. 

First create a new parameter for status[neq]:

Then create your data connection by the parameter:

Next convert the query to function:

Then create a new query with all your want status[neq]:

Use invoke custom function to add new column:

For more details, please refer to https://radacad.com/custom-functions-made-easy-in-power-bi-desktop

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[hexitated]

Thank you very much!

[lbendlin]

Not sure what the size impact is but I would ignore the status filter until AFTER you completed the data pull, and apply it as a next step in Power Query.