Thanks @v-yingjl
It worked for most of the queries, I was able adjust the Fomulas I am calling in my iteration. But one creates an error: "Preview.Error: The type of the current preview value is too complex to display.
Details:"
I've tried to replicate it multiple times with changes back to the original code and doing the changes one by one, but for some reason this is the only query that causes the issue (even though there are other queries with more pages than this one).
To put it side by side, the old formula (without relative path) works just fine:
(InputNumber)=>
let
APIKey = API_Key,
APPKey= APP_Key,
Page="&page="&Number.ToText(InputNumber),
Source = Json.Document(Web.Contents(
"https://api.pipelinecrm.com/api/v3/deals.json?api_key="&APIKey&"&app_key="&APPKey&Page)),
#"Converted to Table" = Table.FromRecords({Source})
in
#"Converted to Table"
But the new one...(PageRunnerDeals):
(InputNumber)=>
let
APIKey = API_Key,
APPKey= APP_Key,
Page=Number.ToText(InputNumber),
Source = Json.Document(Json.Document(
Web.Contents
("https://api.SOFTWARE.com/api",
[
RelativePath = "v3/deals.json",
Query = [api_key=APIKey,app_key=APPKey,page=Page]
]
)
)),
#"Converted to Table" = Table.FromRecords({Source})
in
#"Converted to Table"
Creates the error when called in the following query, as of step "= Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Page", "Funct"}, {"Page", "Funct"})":
let
MAXPages = let
APIKey = API_Key,
APPKey= APP_Key,
Page="&page="&Number.ToText(1),
Source = Json.Document(Web.Contents("https://api.pipelinecrm.com/api/v3/deals.json?api_key="&APIKey&"&app_key="&APPKey&Page)),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{1}[Value],
pages1 = Value[pages]
in
pages1,
Source = List.Generate(
()=> [Page=1, Funct = PageRunnerDeals(1)],
each [Page]<=MAXPages,
each [Page=[Page]+1, Funct = PageRunnerDeals([Page]+1)]),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Page", "Funct"}, {"Page", "Funct"}),
#"Expanded Column1.Funct" = Table.ExpandTableColumn(#"Expanded Column1", "Funct", {"entries", "pagination"}, {"entries", "pagination"}),
#"Expanded Column1.Funct.entries" = Table.ExpandListColumn(#"Expanded Column1.Funct", "entries")
in
#"Expanded Column1.Funct.entries"
