JSON API and function DO - WHILE

[Alex_Yatlen]

Hello everyone

Please help me to configure the correct function DO/WHILE in Power Query

I am trying to play a function from a video: https://www.youtube.com/watch?v=vhr4w5G8bRA

But the result of the function is an incorrectly limited list, referring to rest api (JSON) - the data is contained in 131 rows (one table have 100 rows by API limits=100), which corresponds to 2 tables, and the function creates more than 500 tables.

M code Generate Function:

= List.Generate( () =>
[Result = try GetData_Counter_Party(0) otherwise null, Page = 0],
each [Result] <> null,
each [Result = try GetData_Counter_Party([Page]+100) otherwise null, Page = [Page]+100],
each [Result])

GetData_Counter_Party function:

= (Number_offset as number) as table =>

let
Source = Json.Document(Web.Contents("https://online.*****/counterparty?limit=100&offset="&Number.ToText(Number_offset)&"")),
rows = Source[rows],
#"Converted to Table" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
#"Converted to Table"

Please help me to fix the code so that it does not create lines for empty api answers

[Alex_Yatlen]

Hello everyone

Please help me to configure the correct function DO/WHILE in Power Query

I am trying to play a function from a video: https://www.youtube.com/watch?v=vhr4w5G8bRA

But the result of the function is an incorrectly limited list, referring to rest api (JSON) - the data is contained in 131 rows (one table have 100 rows by API limits=100), which corresponds to 2 tables, and the function creates more than 500 tables.

M code Generate Function:

= List.Generate( () =>
[Result = try GetData_Counter_Party(0) otherwise null, Page = 0],
each [Result] <> null,
each [Result = try GetData_Counter_Party([Page]+100) otherwise null, Page = [Page]+100],
each [Result])

GetData_Counter_Party function:

= (Number_offset as number) as table =>

let
Source = Json.Document(Web.Contents("https://online.*****/counterparty?limit=100&offset="&Number.ToText(Number_offset)&"")),
rows = Source[rows],
#"Converted to Table" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
#"Converted to Table"

Please help me to fix the code so that it does not create lines for empty api answers

[v-lid-msft]

Hi @Alex_Yatlen ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft]

Hi @Alex_Yatlen ,

Could you please try to change the "each [Result] <> null"  to  the following:

each if [Result]  = null then false else if Table.RowCount([Result])=0 then false else true

If it does not work, could you please check the result of the GetData_Counter_Party function if the Number_offset  is 300 or 400?

Please don't have any Confidential Information or Real data in your reply.

Best regards,