SharePoint Online is a powerful platform for collaborative work, but when dealing with large datasets in a SharePoint list, you may encounter limitations in fetching more than 5000 items in a single request. Fortunately, the OData.nextLink feature provides a solution to efficiently retrieve all the items without hitting these limitations. In this blog post, we'll explore how to fetch more than 5000 items in a SharePoint list using OData.nextLink in Power Query, ensuring a seamless and effective data retrieval process.

Step 1: Setting Up Parameters
Start by defining the necessary parameters such as the base URL, field selection, and other configurations. Ensure that you replace placeholders like {tenantName} and 'listID' with your actual SharePoint Online details.

let
    // Define parameters
    baseurl = "https://{tenantName}.sharepoint.com/sites/timekeeping/_api/web/lists/GetById('listID')/",
    fieldselect = "?$top=5000&$select=Id,WeekStartDate,Initiative_x003a_Client,Account_x002d_Initiative,Hours,CreatedBy/Title,CreatedBy/EMail,TaxCatchAll/Term&$expand=CreatedBy,TaxCatchAll&$orderby=Id desc",

Step 2: Creating the GetOData Function
Define a function named GetOData that takes a query as a parameter and utilizes OData.nextLink for fetching data. This function will make a web request and return the result in JSON format.

    // Get data using odata.nextLink
    GetOData = (query as text) =>
        let
            relativePath="items",
            queryOptions=query,
            result = Json.Document(Web.Contents(baseurl,
            [
                RelativePath=relativePath & queryOptions,
                Headers=[Accept="application/json"]
            ]
            ))
        in
            result,

Step 3: Fetching Initial Set of Items
Retrieve the initial set of items using the field selection parameters.

    // Fetch initial set of items
    initialResults = GetOData(fieldselect),

Step 4: Fetching All Results Using List.Generate
Use List.Generate to continuously fetch items as long as the OData.nextLink is not null.

// Get all results using List.Generate only if nextLink is not null
allResults = List.Generate(
     () => [nextLink = try initialResults[odata.nextLink] otherwise null, items = initialResults[value]],
     each List.Count([items]) > 0,
     each
         let
             nextLink = [nextLink],
             nextLinkParameters = if nextLink <> null then Text.AfterDelimiter(nextLink, "items") else null,
             nextItems = try GetOData(nextLinkParameters)[value] otherwise {}
         in [nextLink = try GetOData(nextLinkParameters)[odata.nextLink] otherwise null, items = nextItems],
     each [items]
    ),

Step 5: Combining Initial and All Results
Combine the initialResults with allResults into a single table.

// Combine initialResults with allResults
combinedTable = Table.FromList(List.Combine({allResults}), Splitter.SplitByNothing(), {"value"}),

Step 6: Expanding and Cleaning Up the Table
Expand the 'value' column and clean up the table for a structured dataset.

// Expand and clean up the table
#"Expanded value" = Table.ExpandListColumn(combinedTable, "value")
in
    #"Expanded value"

Conclusion:
By following these steps, you can efficiently fetch more than 5000 items from a SharePoint list using OData.nextLink in Power Query. This approach ensures that you overcome the limitations imposed by SharePoint Online and obtain a complete dataset for your analysis or reporting needs. Implementing this method will enhance the reliability and performance of your data retrieval process from SharePoint lists.