Forum Discussion
Rest API connecting to Power BI
Hi toovishant,
Thank you for sharing the details and walking us through your approach.
From your explanation, it looks like you’re trying to loop through multiple pages of a REST API using List.Generate, which is a valid strategy in Power Query. That said, if the loop isn't working as expected, there are a few areas worth reviewing:
Variable Evaluation in List.Generate: Ensure that the PageNumber used inside your FetchPage function is properly updated in each iteration. In Power Query, the way state is passed in List.Generate can sometimes lead to unexpected behavior if not carefully handled.
Termination Logic: You’re using a hardcoded MaxPages = 10, which is fine for testing, but in production, it’s more reliable to exit based on actual data e.g., when a returned page has fewer than the expected PageSize or is empty.
Function Output Validation: It might help to validate if FetchPage() returns what you expect (a list, table, or record) and whether the "results" field always exists. A missing field or mismatched structure could silently break the loop.
Diagnostics: As a next step, consider testing your FetchPage() function independently for a couple of page numbers to verify consistency in the returned structure. That will help isolate whether the issue is with pagination or with the base fetch.
Kindly refer to the below documentation link for better understanding:
List.Generate - PowerQuery M | Microsoft LearnIf you have any questions regarding this, please feel free to reach out to us. We will be happy to help you. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
Here I tried to create a loop to get all Page dataset. Bu this is not working, please can you guide if this has to be refined or is this the approach i have to use.
let
// Function to fetch data for a given page number
FetchPage = (PageNumber as number) =>
let
Source = Json.Document(Web.Contents("https://---------------ces",
[
Query = [PageNumber = Text.From(PageNumber), PageSize = "10000"],
Headers = [
#"accept" = "*/*",
#"x-api-key" = "-------------"
]
]
)),
Data = Source[results]
in
Data,
// Initialize variables
PageSize = 10000,
MaxPages = 10, // Assuming the API limits to 10 pages
PageNumber = 1,
Result = FetchPage(PageNumber),
AllData = Result,
// Loop to fetch all pages within the limit
Loop = List.Generate(
() => [PageNumber = 2, Result = FetchPage(2)],
each [PageNumber] <= MaxPages and List.Count([Result]) > 0,
each [PageNumber = [PageNumber] + 1, Result = FetchPage([PageNumber])],
each [Result]
),
// Combine all pages
CombinedData = List.Combine({AllData} & Loop),
// Convert to table and expand columns
#"Converted to Table" = Table.FromList(CombinedData, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Thanks,
Vishant
Hi toovishant,
Thank you for sharing the details and walking us through your approach.
From your explanation, it looks like you’re trying to loop through multiple pages of a REST API using List.Generate, which is a valid strategy in Power Query. That said, if the loop isn't working as expected, there are a few areas worth reviewing:
Variable Evaluation in List.Generate: Ensure that the PageNumber used inside your FetchPage function is properly updated in each iteration. In Power Query, the way state is passed in List.Generate can sometimes lead to unexpected behavior if not carefully handled.
Termination Logic: You’re using a hardcoded MaxPages = 10, which is fine for testing, but in production, it’s more reliable to exit based on actual data e.g., when a returned page has fewer than the expected PageSize or is empty.
Function Output Validation: It might help to validate if FetchPage() returns what you expect (a list, table, or record) and whether the "results" field always exists. A missing field or mismatched structure could silently break the loop.
Diagnostics: As a next step, consider testing your FetchPage() function independently for a couple of page numbers to verify consistency in the returned structure. That will help isolate whether the issue is with pagination or with the base fetch.
Kindly refer to the below documentation link for better understanding:
List.Generate - PowerQuery M | Microsoft Learn
If you have any questions regarding this, please feel free to reach out to us. We will be happy to help you. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
Hi toovishant,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi toovishant,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi toovishant,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.