Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
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
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).
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