How can I load in a API with multiple pages in Power BI

[johnw230873]

Hi Guys, I'm working on creating some Terraform reports and one of the things I want to be able to do is extract all the workgroups. I have the API but due to restrictions in the API I can only pull out a max of 100 records per request and we have 135 workspaces :).

I know that these 2 queries will pull out all the data but how best can I represent these in a single table.

https://app.terraform.io/api/v2/organizations/org/workspaces?page[size]=100&page[number]=1

https://app.terraform.io/api/v2/organizations/org/workspaces?page[size]=100&page[number]=2

I thought I could use url parts but when I run this, it only returned the results on the second API call (eg page 2).

let
Source = Json.Document(Web.Contents("https://app.terraform.io/api/v2/organizations/org/workspaces?page[size]=100&page[number]=1" & "https://app.terraform.io/api/v2/organizations/org/workspaces?page[size]=100&page[number]=2", [Headers=[#"Content-Type"="application/vnd.api+json", Authorization="Bearer  11111111111111111111111111111111111111111111111111111111111111"]])),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"attributes"}, {"Column1.attributes"}),
#"Expanded Column1.attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.attributes", {"name"}, {"Column1.attributes.name"})
in
#"Expanded Column1.attributes"

Any idea how I can combine muitple call API datasources into one table?

[selimovd · Accepted solution]

Hey @johnw230873 ,

you can write a function in Power Query that is doing the transformation for each page.

Then you loop 135 times over this function with changing parameter. Take a look at the following blog article on how to do that:

How to loop through an API with Power BI without knowing last page – PBI Guy (pbi-guy.com)

That should get the job done.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[johnw230873]

OMG, I had a look at that link and it took about an hour but got there in the end and I've implemented the dynamic approach. For anyone with this kind of issue the post above it great as if I wanted to cheat I could have also saved a lot of time and just hardcoded the page limits in (shown in the post).

[selimovd]

Hey @johnw230873 ,

I'm happy to hear that you could solve it.

Yes, it's not easy, but the article mentioned above from Kristian Bubalo is by far the best approach in my eyes.

Best regards

Denis