Forum Discussion
Iterating through a list and perform loops on each element
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!
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!
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?
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!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!
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!