Power Query - Multiple request for different parameter values-CodePudding

I'm novice in using Power Query for REST API and have a Power BI report to amend to bring in additional data. I have developed a query and custom function to return the data, which works fine for a single STATUS request parameter value. This is in the following statement;

Source = Json.Document(Web.Contents("https://api.clockify.me/api/v1" & "/workspaces/"& Workspace&"/approval-requests?page="&Page&"&page-size="&PageSize&"&status="&Status,
                          [Headers=[#"X-API-Key"=#"X-API-Key",
                                     #"Content-Type"="JSON"]]
                                     )
                                     ),

However I need to return values for all four status codes (APPROVED,PENDING,REJECTED,APPROVAL_WITHDRAWN") in my query . Clockify advise multiple parameter values are not supported for this API and that I would need to pull a request for each status separately. Unfortunately I'm at a loss how to restructure my queries to do this, so would be really grateful for any advice.

Thanks

Full query and custom function shown below;

let
    Source = "",
    Custom1 = List.Numbers(1 as number, 50 as number) as list,
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each get_page_fx([Column1])),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Column1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Errors",{"Column1"}),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Columns", {"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Errors1", "Data", {"id", "workspaceId", "dateRange.start", "dateRange.end", "owner.userId", "owner.userName", "owner.timezone", "owner.startOfWeek", "status.state", "status.updatedBy", "status.updatedByUserName", "status.updatedAt", "status.note"}, {"Data.id", "Data.workspaceId", "Data.dateRange.start", "Data.dateRange.end", "Data.owner.userId", "Data.owner.userName", "Data.owner.timezone", "Data.owner.startOfWeek", "Data.status.state", "Data.status.updatedBy", "Data.status.updatedByUserName", "Data.status.updatedAt", "Data.status.note"})
in
    #"Expanded Data

(Page as text) =>

let
  
     Source = Json.Document(
            Web.Contents("https://api.clockify.me/api/v1" & "/workspaces/"& Workspace&"/approval-requests?page="&Page&"&page-size="&PageSize&"&status="&Status,
                  [Headers=[#"X-API-Key"=#"X-API-Key",
                             #"Content-Type"="JSON"]]
                             )
                             ),
 
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "workspaceId", "dateRange", "owner", "status"}, {"id", "workspaceId", "dateRange", "owner", "status"}),
    #"Expanded dateRange" = Table.ExpandRecordColumn(#"Expanded Column1", "dateRange", {"start", "end"}, {"dateRange.start", "dateRange.end"}),
    #"Expanded owner" = Table.ExpandRecordColumn(#"Expanded dateRange", "owner", {"userId", "userName", "timezone", "startOfWeek"}, {"owner.userId", "owner.userName", "owner.timezone", "owner.startOfWeek"}),
    #"Expanded status" = Table.ExpandRecordColumn(#"Expanded owner", "status", {"state", "updatedBy", "updatedByUserName", "updatedAt", "note"}, {"status.state", "status.updatedBy", "status.updatedByUserName", "status.updatedAt", "status.note"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded status",{{"id", type text}, {"workspaceId", type text}, {"dateRange.start", type datetime}, {"dateRange.end", type datetime}, {"owner.userId", type text}, {"owner.userName", type text}, {"owner.timezone", type text}, {"owner.startOfWeek", type text}, {"status.state", type text}, {"status.updatedBy", type text}, {"status.updatedByUserName", type text}, {"status.updatedAt", type datetime}, {"status.note", type any}})
in
    #"Changed Type"

CodePudding user response：

hard code status into the function. Duplicate that function 3 more times, with different status in each and different query names. In the main query, you add a column for get_page_fx([Column1])). Add three more columns, one by one, each calling one of the three new functions: get_page_fx2([Column1])) get_page_fx3([Column1])) and get_page_fx4([Column1])). Thats all the data. If you want, you can them combine the columns with append