Power BI调用Azure API获取VM列表遇nextLink分页问题求助

Hey there! As a fellow Power BI user who's dealt with Azure API pagination before, I totally get how confusing this can be when you're just starting out. Let me walk you through exactly how to handle the nextLink pagination for Azure's VM API in Power BI using M code.

First, let's break down the core issue: Most Azure management APIs (including the virtual machine list API) return a maximum of 50 results per page by default. When more data exists, the response includes a nextLink field with the URL to fetch the next batch. We need to keep calling this URL until nextLink disappears from the response.

Here's a step-by-step implementation:

1. Authenticate to Azure AD (if required) #

Nearly all Azure management APIs need an access token. I'll use the client credentials flow here (common for service-to-service access), but you can adjust this if you're using user-based authentication.

2. Create a reusable function for single-page API calls #

We'll wrap the API request in a function to avoid repeating code for each page. This function takes a URL, fetches the data, and returns both the page results and the nextLink (if it exists).

3. Use List.Generate to loop through all pages #

Power Query's List.Generate is perfect for this scenario—it lets us start with the initial API call, keep fetching new pages as long as nextLink exists, and collect all results in one place.

Full M Code Example #

let
    // --- Replace these values with your Azure details ---
    TenantID = "your-tenant-id",
    ClientID = "your-client-id",
    ClientSecret = "your-client-secret",
    SubscriptionID = "your-subscription-id",
    API_Version = "2023-07-01", // Use the latest stable API version
    
    // Step 1: Fetch Azure AD access token
    TokenURL = "https://login.microsoftonline.com/" & TenantID & "/oauth2/token",
    TokenResponse = Json.Document(Web.Contents(TokenURL, [
        Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
        Content = Text.ToBinary(Uri.BuildQueryString([
            grant_type = "client_credentials",
            client_id = ClientID,
            client_secret = ClientSecret,
            resource = "https://management.azure.com/"
        ]))
    ])),
    AccessToken = TokenResponse[access_token],

    // Step 2: Define function to get a single API page
    GetAPIPage = (url as text) =>
        let
            APIResponse = Json.Document(Web.Contents(url, [
                Headers = [
                    #"Authorization" = "Bearer " & AccessToken,
                    #"Content-Type" = "application/json"
                ]
            ])),
            PageData = APIResponse[value],
            // Use try/otherwise to handle cases where nextLink doesn't exist
            NextPageLink = try APIResponse[nextLink] otherwise null
        in
            [Data = PageData, NextLink = NextPageLink],

    // Step 3: Initial API call URL for all VMs in your subscription
    InitialAPIURL = "https://management.azure.com/subscriptions/" & SubscriptionID & "/providers/Microsoft.Compute/virtualMachines?api-version=" & API_Version,
    FirstPage = GetAPIPage(InitialAPIURL),

    // Step 4: Generate all pages using List.Generate
    AllPages = List.Generate(
        () => FirstPage, // Start with the first page
        each [NextLink] <> null, // Continue as long as there's a nextLink
        each GetAPIPage([NextLink]), // Fetch the next page
        each [Data] // Extract the data from each page
    ),

    // Step 5: Combine all pages into a single list of records
    CombinedRecords = List.Combine(AllPages),
    // Convert the combined records to a table
    FinalTable = Table.FromRecords(CombinedRecords)
in
    FinalTable

Key Notes to Keep in Mind: #

• Privacy Settings: When you first run this code, Power BI will prompt you to set privacy levels for the Azure endpoints. Set them to "Organizational" to avoid cross-source privacy errors.
• API Version: Always use the latest stable API version (check Azure's official docs) to ensure the nextLink format is fully supported.
• Authentication Adjustments: If you're using user-based OAuth instead of client credentials, you can skip the token-fetching section—Power BI will handle authentication automatically when you connect to the API directly.
• Rate Limits: Azure APIs have rate limits, but for VM pagination, this shouldn't be an issue unless you have thousands of VMs. If you hit limits, add a small delay using Function.InvokeAfter inside the GetAPIPage function.

This code will automatically fetch all your virtual machines by following the nextLink until there's no more data to retrieve.

内容的提问来源于stack exchange，提问作者Beefcake