Search with pagination in Power BI

[ariannequon]

Hi! If you saw my previous posts, I’m working on using the search API within Power BI. I haven’t worked with APIs before in the past, so would appreciate any guidance. We’re pretty set on using the APIs within Power BI directly rather than a custom connector. My current idea is getting the total number of pages needed to get all the data and looping my function until I have all identities. However, I’m running into some issues with my code and would love some help.

Here is my full code:

let
    access_token = #"token from previous step",

    fetchPage = (nextID as nullable text, limit as number, pagenum as number) =>
    let 
        api_url = "some_url",
        searchAfterPart = if pagenum >1 then Text.Format(", ""searchAfter"": [""{0}""]", {nextID}) else "", // adding this to the body is when it stops working
        headers = [
            #"Authorization" = "Bearer " & access_token,
            #"Content-Type" = "application/json"
        ],
        body = Text.ToBinary(
            "{
                ""indices"": [""identities""],
                ""query"": {
                    ""query"": ""*"" 
                },
                ""sort"": [""id""] " & searchAfterPart & "
            }"
        ),
        response = Json.Document(Web.Contents(api_url, [Headers=headers, Content=body])),
        data = Table.FromList(response, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        expand = Table.ExpandRecordColumn(data, "Column1", {"id", "other columns of interest"}),
        newNextID = if Table.RowCount(expand) > 0 then expand{Table.RowCount(expand) - 1}[id] else null
    in
        [data = expand, nextID = newNextID],  

    limit = 250,
    count = countIdentities, 
    totalPages = Number.RoundUp(count / limit),  

    // Fetch the first page of data, structured this way because including it in the loop was causing problems earlier as well
    firstPage = fetchPage(null, limit, 1), 
    initialData = firstPage[data],      
    initialNextID = firstPage[nextID],   

    // Loop to fetch subsequent pages, with page counter
    allData = List.Generate(
        () => [data = initialData, nextID = initialNextID, currentPage = 1],  
        each [nextID] <> null and [currentPage] <= totalPages,  // Continue as long as there are pages left and currentPage <= totalPages
        each let
            pageData = fetchPage([nextID], limit, [currentPage]+1),  // Fetch next page
            newData = Table.Combine({[data], pageData[data]}),  // Combine current data with new page data
            newNextID = pageData[nextID]  // Get nextID for the next iteration
        in
            [data = newData, nextID = newNextID, currentPage = [currentPage]+1],  // Move to the next page
        each [data]  // Return the accumulated data
    ),
    #"Converted to Table" = Table.FromList(allData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"id", "other columns of interest"}) 
    
in
    #"Expanded Column1"

Here are the issues and observations I have:

1. The code as is returns an error after the first call, so only the first call outside of the loop to fetchPage is successful:
   “Expression.Error: The column ‘Column1’ of the table wasn’t found.
   Details:
   Column1”

2. If I set a static value to searchAfter and define it within the body, the code is able to loop properly and do the correct amount of calls, returning the same data every time due to the static search after value

body = Text.ToBinary(
            "{
                ""indices"": [""identities""],
                ""query"": {
                    ""query"": ""*"" 
                },
                ""sort"": [""id""] ",
                 **""searchAfter"" : ""some static id""**
            "}"
        ),

3. If I set a static value to searchAfter and define it within my searchAfterPart, I receive a 400 error:
   DataSource.Error: Web.Contents failed to get contents from ‘https://hostname.api.identitynow.com/v3/search?limit=250’ (400):
   Details:
   DataSourceKind=Web
   DataSourcePath=https://hostname.api.identitynow.com/v3/search
   Url=https://hostname.api.identitynow.com/v3/search?limit=250

[mcheek (Mark Cheek)]

Just curious, does this work without the use of the searchAfter? Meaning up to 10k records?

I have a working pagination example in this topic if you want to try and adapt it to your use case

Extracting Data from IDN to PowerBI

@jodi_pruitt The trickiest part is that Power BI’s “Web” source does not allow oauth-type authentication, only basic and api key-based auth. In order to get around this, I had to create a “Blank Query” and do everything in Power Query. Here are some of the snippets… First, you need to get a token using your client credentials getToken = Json.Document( Web.Contents( param_root_uri, [ RelativePath="/oauth/token", Query=[ …

[ariannequon]

Hi Mark, thanks so much for your reply! I tried out your code and it works great for me up to the limit of 10k. When able to execute (ex. using the static searchAfter value in the query) my code is able to return over 10k results.

[ariannequon]

I’ve gotten a working solution on this now! Had to alter the formatting of the searchAfter string and add extra checks for content, but I’m now able to paginate past the 10k limit and grab all search results (around 19k for our tenant).

let
    access_token = #"getToken-prod",  // Use the token retrieved from another query
    limit = 250,
    
    fetchPage = (nextID as nullable text, limit as number, pagenum as number) =>
    let 
        textLimit = Number.ToText(limit),
        api_url = "https://host.api.identitynow.com/v3/search?limit=" & textLimit & "",
        
        searchAfterPart =  if pagenum >1 and nextID <> null then
             """" & Text.From(nextID) & """" 
        else "", 
        headers = [
            #"Authorization" = "Bearer " & access_token,
            #"Content-Type" = "application/json"
        ],
        body = Text.ToBinary(
            "{
                ""indices"": [""identities""],
                ""query"": {
                    ""query"": ""*"" 
                },
                ""sort"": [""id""], 
               ""searchAfter"": ["&searchAfterPart&"]
             
            }"
        ),
        response = Json.Document(Web.Contents(api_url, [Headers=headers, Content=body])),
        responseData = 
            if List.Count(response) > 0 
    then Table.FromList(response, Splitter.SplitByNothing(), null, null, ExtraValues.Error) 
    else Table.FromRecords({}),
    expand = 
    if Table.HasColumns(responseData, "Column1") 
    then Table.ExpandRecordColumn(responseData, "Column1", {"id", other columns of interest}))
    else responseData,
        newNextID = if Table.RowCount(expand) > 0 then expand{Table.RowCount(expand) - 1}[id] else null
    in
        [data = expand, nextID = newNextID],

  
    count = countIdentities, // Retrieved from another query
    totalPages = Number.RoundUp(count / limit),

    // Fetch the first page of data
    firstPage = fetchPage(null, limit, 1),
    initialData = firstPage[data],
    initialNextID = firstPage[nextID],

    // Loop to fetch subsequent pages, with page counter
    allData = List.Generate(
        () => [data = initialData, nextID = initialNextID, currentPage = 1],  // Start with the first page data and page 1
        each [nextID] <> null and [currentPage] <= totalPages, 
        each let
            pageData = fetchPage([nextID], limit, [currentPage]+1),
            newData = Table.Combine({[data], pageData[data]}),
            newNextID = pageData[nextID] 
        in
            [data = newData, nextID = newNextID, currentPage = [currentPage] + 1], 
        each [data]  // Return the accumulated data
    ),
    #"Converted to Table" = Table.FromList(allData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"id", other columns of interest}),
in
#"Expanded Column1"