Forum Discussion
Rest API connecting to Power BI
Hi Team,
I am trying to connect Rest API to Power BI. Currently I am using below query trying to connect via "Blank Query"
But below query gives only 10k records. Also when I try to connect WEB, not sure on the "Key" details to connect
let
Source = Json.Document(Web.Contents("https://-------------------PageNumber=1&PageSize=50000",
[
Headers = [
#"accept" = "*/*",
#"x-api-key" = "------------------"
]
]
))
in
Source
When trying to connect via WEB, not sure what type of Key details need to be add here.
Please advice
Any help is very much Apperciated.
Thank You,
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 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.
11 Replies
Which REST API is that? Does it come with documentation?
That's right, I have to reffer the Documentation via Swagger API does this help?
Do you have a link to the documentation?
Hi toovishant,
Thank you for reaching out to the Microsoft Fabric Community, and thanks to lbendlin, for their valuable inputs on this thread.
From your screenshot, it looks like you're trying to use the Web connector in Power BI and are unsure what to enter in the Key field under Web API authentication.
Since you've already managed to successfully call the API using a Blank Query with Web.Contents and a custom x-api-key header, you're absolutely on the right path. This approach is often preferred when working with APIs that require custom headers, which the standard Web connector interface doesn’t always support well.
If you'd still like to explore using the Web connector UI, here's how you can proceed:
- Select Web API as the authentication method.
- In the Key field, enter the value of your API key (no quotes).
- However, keep in mind that some APIs (like yours) require the key to be passed using a custom header (e.g., x-api-key). The Web connector may not allow for that directly — in which case, your current method using Web.Contents in a Blank Query remains the more flexible and reliable option.
Kindly refer to the below mentioned document links for better understanding:
Power Query Web connector - Power Query | Microsoft Learn
Web.Contents - PowerQuery M | Microsoft Learn
Regarding the 10,000 Record Limit: The reason you're receiving only 10,000 records is likely due to pagination settings enforced by the API. Most REST APIs use parameters like PageNumber, offset, or nextLink to return data in chunks or pages.Since you mentioned the API is documented using Swagger, I recommend reviewing how pagination is handled there. Once you identify the correct parameter (e.g., ?PageNumber=2, ?offset=10000, etc.), we can help you write a query in Power BI to loop through all pages and retrieve the full dataset.
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.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 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.
v-kpoloju-msft - Apperciate your help. After checking I see there is a limitation and need request the second page and so on, changing the parameters into the url. Do you know if I can create a loop to get this data with same query.
Thank You,
Vishant
Yeah, you can connect to Power BI using the REST API, but it’s mostly for managing stuff like datasets, reports, and refreshes - not for pulling data directly into reports. For that, you'd usually use connectors like SQL, SharePoint, etc.
