Looping thru a table to perform a REST API call with paging

[dusdau]

Hi All,

I've been working on this one a while, and have found many good posts and videos to help but I can't quite get this to the finish line so hoping you can help.

I have a query called 'PhishingTests' as follows:

pst_id	total_pages
121	5
123	8
134	6
141	3

pst_id represents the test id I need to get results for, and total_pages is the number of pages I need to get.  I have created a function to do this as follows:

The function works...the part I can't figure out is exactly how to use the 'PhishingTests' query to provide the parameters to loop thru the function to get all the results.

I have tried adding column and invoking custom function from 'PhishingTests' but I get an ugly error.

I assume this is due to the recursive nature of the function colliding with invoking it from the query.

I have also tried calling it from another query, like so:

let
testID = PhishingTests[pst_id],

totalPages = PhishingTests[total_pages],
results = List.Transform(testID, each fnGetTestResults(_, totalPages, 1))
in
results

However I am unable to get this to also pass thru the relative total_pages value for the function.  It errors since I think its trying to pass a list, erroring with "Expression.Error: We cannot convert a value of type List to type Number."

Any ideas how to structure this to make this work would be gratefully received! 

Thanks,

Dustin 

[lbendlin · Accepted solution]

General approach is to use Table.AddColumn on your PhishingTests table where you call the fnGetTestResults and get the contents back into the new table cell (as a table in a table). Then you can decide if you just want to combine the values in that column, or do other fancy stuff with it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVNJRMlWK1QGxjYFsCwjb2ATINoOwTUBqjJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [pst_id = _t, total_pages = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Content", each fnGetTestResults([pst_id],[total_pages],1))
in
    #"Added Custom"

Are you sure your API calls return JSON pages that an be converted into tables without issue?

[lbendlin · Accepted solution]

General approach is to use Table.AddColumn on your PhishingTests table where you call the fnGetTestResults and get the contents back into the new table cell (as a table in a table). Then you can decide if you just want to combine the values in that column, or do other fancy stuff with it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVNJRMlWK1QGxjYFsCwjb2ATINoOwTUBqjJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [pst_id = _t, total_pages = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Content", each fnGetTestResults([pst_id],[total_pages],1))
in
    #"Added Custom"

Are you sure your API calls return JSON pages that an be converted into tables without issue?

[dusdau · Accepted solution]

Thanks lbendlin! What you gave me is what I needed to focus on.  Here is what I finally got working:

Add Column -> Invoke Custom Function

Function 1

Function 2

Seems to work great!  Thanks again!  Hope this helps someone else...

[lbendlin]

By the way, 

List.Combine({results,{testResults}})

is a bit rich. You should be able to write:

results & testResults

to get the same output.