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 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!