Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Amend API query to return multiple statuses

Hi 

I'm new to Power Query and have written the following to extract data from a time recording app we use called Clockify.

 

(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"]]
                                      )
                                       ),

I need to return records for all status codes (APPROVED,PENDING,REJECTED,WITHDRAWN_APPROVAL) however I don't understand how to structure the query to include an OR clause. At the moment I have a parameter called Status and the query works fine  if populate with one of the statuses e.g. APPROVED. The API has a default value of PENDING, so only returns those records if Status is omitted from query.

 

Be grateful for any help or advice anyone can offer.

Best regards

Mark

 

 

 

 

 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    Does the documentation for this API provides the syntax for or ? Alternatively, you can make a one column table of the your status values and then invoke your function in a new column, passing the status values in on each row.

     

    Pat

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    Does the documentation for this API provides the syntax for or ? Alternatively, you can make a one column table of the your status values and then invoke your function in a new column, passing the status values in on each row.

     

    Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Pat 

    Thanks for the reply.

     

    The syntax I've been provided with is as follows

     

     

    I've tried passing everything following the "=" sign in the parameter but only get records returned for the first status.

     

    Could you please elaborate on your suggestion regarding the one column table as it's above my current level of knowledge.

     

    Many thanks for your help.

     

    Best regards

     

    Mark

     

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Pat

     

    I have questioned the syntax provided by Clockify and they now advise that returning multiple status is not supported so request must be made for each status separately.

     

    I will close this query now and go away and research how I can do this, and submit another question if I can't figure it.

     

    Thanks again 

     

    Mark