topic API loop with List.Empty - access column from previous step in Developer

API loop with List.Empty - access column from previous step

MarkusEng1998 — Fri, 03 May 2024 15:48:36 GMT

Thanks to @lbendlin, I found a tip to terminate an API loop with List.Empty.

However my reference to the projNo column in the List.Generate() is erroring when I expand the table list.

#"02ListGenerate = Table.AddColumn(#"01filterProject", "dRofusRecord", each List.Generate(() => [proj = [projNo], skip = 0, top = 50, recordList = getURI(proj, top, skip)], each not List.IsEmpty([recordList]), each [proj=[projNo], skip = [skip] + [top], top = [top], recordList = getURI(proj, top, skip)] ))

Thank you for any guidance.

Re: API loop with List.Empty - access column from previous step

lbendlin — Fri, 03 May 2024 15:52:38 GMT

What's the error message?

Re: API loop with List.Empty - access column from previous step

MarkusEng1998 — Fri, 03 May 2024 16:58:36 GMT

Expression.Error: The field 'projNo' of the record wasn't found.

getURI()

(projNo as text, topVal as number, skipVal as number)=> let #"FilterColumnTable" = Table.SelectRows(#"102APImapRooms100", each ([ObjectType] = "rooms")), GetWebContents = Web.Contents( "https://api-us.drofus.com/api/company/", [ RelativePath= projNo & "/rooms" & "?$select=" & Text.Combine(#"FilterColumnTable"[dRofus.ID], ",") & "&$top=" & Number.ToText(topVal) & "&$skip=" & Number.ToText(skipVal) ] ), convertedJson = Json.Document(GetWebContents,65001), in convertedJson

Re: API loop with List.Empty - access column from previous step

MarkusEng1998 — Fri, 03 May 2024 17:24:05 GMT

In the List.Generate() I changed proj=[projNo] to projNo = [projNo].

This now works!

= Table.AddColumn(#"01filterProject", "dRofusRecord", each List.Generate(() => [projNo = [projNo], skip = 0, top = 50, recordList = getURI(projNo, top, skip)], each not List.IsEmpty([recordList]), each [projNo = [projNo], skip = [skip] + [top], top = [top], recordList = getURI(projNo, top, skip)] ))

Thank you again @lbendlin for prompting the question. I appreciate this forum because submitting the question forces me to better understand the "problem statement."