Iterating through a list and perform loops on each element

[AlexR_DE]

Hi folks,

I am connecting to ServiceNOW Rest API, which works just fine.

As I have more records than the page size, I am also iterating through the pages.

Currently I have 1 query per REST [pagination] query.

I was successfully converting this logic to a single query elsewhere (on a Sharepoint REST case), for which I simply added a custom column that contained the result of the REST call.

Now I would like to combine both approaches:

I'd like to have a list of values. For each entry I'd like to add a custom column that in turns paginates over the REST API.

It must also refresh in PBI service.

Let me show you the two queries I have:

Service Now pagination:

let
    varCIClass = "cmdb_ci_server",
    Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0],
    //Start Value
   		each List.Count([WebCall][result])>0 or [Counter]=0, // Condition under which the next execution will happen
        each [ WebCall = Json.Document(Web.Contents("https://INSTANCE.service-now.com",            
                                    [
                                        RelativePath = "/api/now/table/"&varCIClass&"?sys_class_name="&varCIClass&"&sysparm_display_value=true&sysparm_limit=10000",
                                        Query=[sysparm_offset=Text.From([Page])]
                                    ]
                                    )), // retrieve results per call
     			Page = [Page]+10000,
     			Counter = [Counter]+1// internal counter
    ]
    ) ,1),
    #"Converted to Table" = if List.IsEmpty(Pagination) then null else Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = if #"Converted to Table" = null then null else Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall"}, {"WebCall"}),
    #"Expanded WebCall" = if #"Expanded Column1" = null then null else Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
    #"Expanded result" = if #"Expanded WebCall" = null then #table(type table[#"result"=any],{}) else Table.ExpandListColumn(#"Expanded WebCall", "result")
in
    #"Expanded result"

this actually paginates through the table and returns unlimited number of records:

I have 47 such queries, which I now want to merge into a single query, by first having a list of classes to iterate through, then paginate for each of the entries.

My query so far looks as follow:

let
    Source = #table({"table"}, {{"cmdb_ci_linux_server"}, {"cmdb_ci_win_server"}}),
    #"Added Custom" = Table.AddColumn(Source, "Query results", each 
                                    // --- NEED PAGINATION HER AS PER THE OTHER QUERY
                                    Json.Document(Web.Contents("https://INSTANCE.service-now.com",            
                                                [
                                                    RelativePath = "/api/now/table/"&[table]&"?sys_class_name="&[table]&"&sysparm_display_value=true&sysparm_limit=10000"
                                                    //,Query=[sysparm_offset=Text.From([Page])]
                                                ]
                                                ))),
                                    // --- END PAGINATION
    #"Expanded Query results" = Table.ExpandRecordColumn(#"Added Custom", "Query results", {"result"}, {"result"}),
    #"Expanded result" = Table.ExpandListColumn(#"Expanded Query results", "result")
in
    #"Expanded result"

It is working, but not with all pages obviously:

So in essence I want to merge the pagination stuff from first query into the second one.

Any advice?

Update: Added the first query - forgot in initial post.

[v-karpurapud]

Hi @AlexR_DE 

Thank you for reaching out to the Microsoft Fabric Community Forum.

To make your second query handle pagination like the first one, you can build the pagination logic directly into the custom column. This way, for each table in your list, Power Query will automatically loop through all the pages of data from the ServiceNow API.

let

    Source = #table({"table"}, {{"cmdb_ci_linux_server"}, {"cmdb_ci_win_server"}}),

    #"Added Custom" = Table.AddColumn(Source, "Query results", each

        let

            Pagination = List.Skip(List.Generate(() => [WebCall=[result = {0}], Page = 0, Counter=0],

                each List.Count([WebCall][result])>0 or [Counter]=0,

                each [

                    WebCall = Json.Document(Web.Contents("https://INSTANCE.service-now.com",           

                        [

                            RelativePath = "/api/now/table/"&[table]&"?sys_class_name="&[table]&"&sysparm_display_value=true&sysparm_limit=10000",

                            Query=[sysparm_offset=Text.From([Page])]

                        ])),

                    Page = [Page]+10000,

                    Counter = [Counter]+1

                ]), 1),

            Results = if List.IsEmpty(Pagination)

                then #table(type table[#"result"=any], {})

                else Table.ExpandListColumn(

                        Table.ExpandRecordColumn(

                            Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

                            "Column1", {"WebCall"}, {"WebCall"}

                        )[WebCall], "result")

        in

            Results, type table [result=any]),

    #"Expanded Results" = Table.ExpandTableColumn(#"Added Custom", "Query results", {"result"}, {"result"})

in

    #"Expanded Results"

This solution embeds the pagination logic inside the Table.AddColumn step, ensuring each table entry paginates through all its records.

If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution.
 

If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.
 

Thank You!

[AlexR_DE]

Hi  v-karpurapud,

thanks for your suggestion.
In principle, this is what I was looking for. I tried your code, but apparently it does not find the [table] column which is in the outer scope. So, we would somehow need to pass the column from the outer scope into the embedded "let" stuff?

[v-karpurapud]

Hi @AlexR_DE 

The issue arises because the [table] reference inside the nested let block of the Table.AddColumn step is not properly accessing the outer scope's column. In Power Query, when you're inside a Table.AddColumn expression, you need to explicitly reference the row's column value using the each keyword's implicit row context. The current code assumes [table] is directly available, but it needs to be scoped correctly. Try the below once:

let
    Source = #table({"table"}, {{"cmdb_ci_linux_server"}, {"cmdb_ci_win_server"}}),
    #"Added Custom" = Table.AddColumn(Source, "Query results", each 
        let
            CurrentTable = [table], // Capture the table value from the outer scope
            Pagination = List.Skip(List.Generate(() => [WebCall=[result = {0}], Page = 0, Counter=0],
                each List.Count([WebCall][result])>0 or [Counter]=0,
                each [
                    WebCall = Json.Document(Web.Contents("https://INSTANCE.service-now.com",            
                        [
                            RelativePath = "/api/now/table/"&CurrentTable&"?sys_class_name="&CurrentTable&"&sysparm_display_value=true&sysparm_limit=10000",
                            Query=[sysparm_offset=Text.From([Page])]
                        ])),
                    Page = [Page]+10000,
                    Counter = [Counter]+1
                ]), 1),
            Results = if List.IsEmpty(Pagination) 
                then #table(type table[#"result"=any], {}) 
                else Table.ExpandListColumn(
                        Table.ExpandRecordColumn(
                            Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
                            "Column1", {"WebCall"}, {"WebCall"}
                        )[WebCall], "result")
        in
            Results, type table [result=any]),
    #"Expanded Results" = Table.ExpandTableColumn(#"Added Custom", "Query results", {"result"}, {"result"})
in
    #"Expanded Results"

Thank You!

[AlexR_DE]

Hi  v-karpurapud,

Sorry for being so dumb, but I never write M-code from Scratch 😕

It is almost working...

Your code returns me an error, when trying to expand:

If I do this for a single row, I am able to expand (but I don't know how to replicate this for each row though):

This is the code fro above screenshot (Working):

let
    Source = #table({"table"}, {{"cmdb_ci_linux_server"}, {"cmdb_ci_win_server"}}),
    #"Added Custom" = Table.AddColumn(Source, "Query results", each 
        let
            CurrentTable = [table], // Capture the table value from the outer scope
            Pagination = List.Skip(List.Generate(() => [WebCall=[result = {0}], Page = 0, Counter=0],
                each List.Count([WebCall][result])>0 or [Counter]=0,
                each [
                    WebCall = Json.Document(Web.Contents("https://INSTANCE.service-now.com",            
                        [
                            RelativePath = "/api/now/table/"&CurrentTable&"?sys_class_name="&CurrentTable&"&sysparm_display_value=true&sysparm_limit=10000",
                            Query=[sysparm_offset=Text.From([Page])]
                        ])),
                    Page = [Page]+10000,
                    Counter = [Counter]+1
                ]), 1),
            Results = if List.IsEmpty(Pagination) 
                then #table(type table[#"result"=any], {}) 
                else Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
				
        in
            Results, type table [result=any]),
    // Following will only expand the first row, instead of all:
	#"Query results" = #"Added Custom"{0}[Query results],
	
	// Rest works as intended
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Query results", "Column1", {"WebCall"}, {"WebCall"}),
    #"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
    #"Expanded result" = Table.ExpandListColumn(#"Expanded WebCall", "result")
in
    #"Expanded result"

I really appreciate your help!

[v-karpurapud · Accepted solution]

Hi @AlexR_DE 

We completely understand your concern regarding the issue . The main problem arises because when you return Table.FromList(Pagination, ...), you still have a table containing lists, rather than a fully expanded result.
 

As a result, when you attempt to expand the column later, it does not work dynamically across all rows  it only succeeds manually for a single row (e.g., using #"Added Custom"{0}[Query results]).
 

To resolve this, it is necessary to process and expand each pagination result within the custom column itself. This ensures that the "Query results" column already contains a fully flattened table, rather than a nested list within a table, allowing dynamic expansion across all rows.
Consider the modified M Code provided in your last response below:

let
    Source = #table({"table"}, {{"cmdb_ci_linux_server"}, {"cmdb_ci_win_server"}}),
    #"Added Custom" = Table.AddColumn(Source, "Query results", each 
        let
            CurrentTable = [table],
            Pagination = List.Skip(List.Generate(
                () => [WebCall = [result = {0}], Page = 0, Counter = 0],
                each List.Count([WebCall][result]) > 0 or [Counter] = 0,
                each [
                    WebCall = Json.Document(Web.Contents(
                        "https://INSTANCE.service-now.com",
                        [
                            RelativePath = "/api/now/table/" & CurrentTable & "?sys_class_name=" & CurrentTable & "&sysparm_display_value=true&sysparm_limit=10000",
                            Query = [sysparm_offset = Text.From([Page])]
                        ]
                    )),
                    Page = [Page] + 10000,
                    Counter = [Counter] + 1
                ]
            ), 1),
            
            ResultsTable = if List.IsEmpty(Pagination) 
                then #table(type table [result = any], {})
                else
                    let
                        PaginationTable = Table.FromList(Pagination, Splitter.SplitByNothing(), {"Column1"}),
                        ExpandedWebCall = Table.ExpandRecordColumn(PaginationTable, "Column1", {"WebCall"}),
                        ExpandedResult = Table.ExpandRecordColumn(ExpandedWebCall, "WebCall", {"result"}),
                        FinalResult = Table.ExpandListColumn(ExpandedResult, "result")
                    in
                        FinalResult
        in
            ResultsTable, type table [result=any]
    ),
    #"Expanded Results" = Table.ExpandTableColumn(#"Added Custom", "Query results", {"result"}, {"result"})
in
    #"Expanded Results"

If this post helpful, kindly mark it as Accepted Solution. 

Thank You!

[AlexR_DE]

Thank you v-karpurapud - this change made it!