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
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!Thank you v-karpurapud - this change made it!