Dynamic function to generate a table by invoking REST services based on parameter

[sceccolini]

Hello,

I defined a function to invoke a REST service which returns a table that contains some other REST service URLs I'm resolving in a table with the Web.Contents function.

This is the source function "MYFUNCTION - Users". The function contains the page parameter I use to iterate on the overall elements.

(Page as number) =>
let
    Source = Json.Document(Web.Contents("https://myRESTServicesURL/users?items-per-page=50&page="&Number.ToText(Page))),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"id", type text}, {"title", type text}, {"author", type any}, {"updated", type datetime}, {"page", Int64.Type}, {"items-per-page", Int64.Type}, {"links", type any}, {"entries", type any}})
in
    #"Changed Type"

and this is the table resulting from this function, defined by the following statement:

= List.Generate(()=>
[Result = try #"MYFUNCTION - Users"(1) otherwise null, Page=1],
each [Result]<>null,
each [Result = try #"MYFUNCTION - Users"([Page]+1) otherwise null, Page=[Page]+1],
each [Result])

Basically, this function returns one REST I am expanding in the above table definition, and this is fine.

Instead of having just the page parameter, I'd like my function to have the "https://myRESTServicesURL/" as a parameter. In a table, I have all the replacements for this parameter in a way in a single function I can generate the overall services to be invoked.

I defined the following function, which is working:

= (Page as number, RESTServiceUsersURL as text) =>
let
    Source = Json.Document(Web.Contents(RESTServiceUsersURL&"/users?items-per-page=50&page="&Number.ToText(Page))),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"id", type text}, {"title", type text}, {"author", type any}, {"updated", type datetime}, {"page", Int64.Type}, {"items-per-page", Int64.Type}, {"links", type any}, {"entries", type any}})
in
    #"Changed Type"

But now I need to generate the table by substituting the 'RESTServiceUsersURL' parameter with all the URLs coming from one table in Power BI. Do you have any suggestions?

Thanks

Simone

[Anonymous]

Hi @sceccolini 

You need to generate a list of tables for each URL and then combine them into one table. You can use 'List.Generate' to iterate over the pages for each URL.

(URL as text) =>
let
// Function to get data for each page
GetData = (Page as number) =>
let
Source = Json.Document(Web.Contents(URL & "/users?items-per-page=50&page=" & Number.ToText(Page))),
Table = Table.FromRecords({Source}),
TypedTable = Table.TransformColumnTypes(Table,{{"id", type text}, {"title", type text}, {"author", type any}, {"updated", type datetime}, {"page", Int64.Type}, {"items-per-page", Int64.Type}, {"links", type any}, {"entries", type any}})
in
TypedTable

// Generate list of pages
Pages = List.Generate(
() => [Result = try GetData(1) otherwise null, Page = 1],
each [Result] <> null,
each [Result = try GetData([Page] + 1) otherwise null,
No file chosen

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sceccolini]

Hi Jayleny,

thanks for the response. I slightly corrected your function by configuring the 'Function  Users' function  with the following script, which now is configured with only the URL parameter:

= (URL as text) =>
let
    // Function to get data for each page
    GetData = (Page as number) =>
    let
        Source = Json.Document(Web.Contents(URL & "/users?items-per-page=50&page=" & Number.ToText(Page))),
        Table = Table.FromRecords({Source}),
        TypedTable = Table.TransformColumnTypes(Table,{{"id", type text}, {"title", type text}, {"author", type any}, {"updated", type datetime}, {"page", Int64.Type}, {"items-per-page", Int64.Type}, {"links", type any}, {"entries", type any}})
    in
        TypedTable
in
    GetData

Now which is the correct statement to generate the table by invoking the above function for each URL defined in the 'Table - Repositories' table ('URLs' column) and to be sure all the pages for each service are iterated?

Thanks

Simone