You can use Excel Power Bi to automatically load query data which is kept up to date every time you open the excel file. 

Once you have built the query, you'll need to create an API key that has access to this query. On how to create an API key, please see: http://help.peoplehr.com/integrations/creating-an-api-key 

You can then link the query within Excel.

1. Create a new excel document

2. Select the 'Data' tab

3. Select 'From Web'

5. Select the 'Advanced' option

6. Copy the URL shown below and paste into the 'URL Parts' text box

Once clicking outside of the text box, your screen should look like this:

7. Click 'OK'. This will bring up the Power Query Editor:

8. Click the 'Home' tab and from here select the 'Advanced Editor', as shown below:

9. You'll then be presented with this screen:

10. Remove all text in this text box. 

11. Copy and paste the following text into this text box (please note that text in bold needs replaces with the specified information):

let  url="https://api.peoplehr.net/Query", 

body="{""APIKey"": ""ENTER API KEY HERE"",""Action"": ""GetQueryResultByQueryName"",""QueryName"":""ENTER QUERY NAME HERE""}",

Source = Json.Document(Web.Contents(url,[

         Headers = [ #"Content-Type"="application/json"],

            Content = Text.ToBinary(body)

            ]

        ))

in Source

12. Click 'Done'.

13. If loaded successfully, the data will be available in the Result section.

14. Click the highlighted “List” link:

15. Select the “To Table” button:

16. From here, ensure the delimiter is set to 'None' and then click 'OK':

17. The loaded items are presented as a list of Records. To enable a more detailed view of the records, click the highlighted Split into Columns button:

18. Include the wanted columns from the presented list and uncheck the “Use original column name as prefix” setting:

19: Data is now loaded in a table:

When setting this up, you may get an error message like this:

The API key allows access from Excel to your specified queries. Therefore if there is a problem with the access, this will be due to one of the following:

To double check the above data is correct, go back into the API settings within People®. From here you can view the Query Endpoint to see if the specific query has been included in this key. You can then also cross reference the query name within here to how you have typed this within PowerBi.  

Thanks,

Customer Services Team.