To ensure you're viewing the most up-to-date data whenever you open a query in Excel, you can use Excel Power BI.
Once you've built the query, you need to create an API key that has access to this query and then you can then link the query to Excel. To do this, follow the steps below.
In Microsoft Excel, create a new sheet.
Click the Data tab then click From Web.
Click Advanced then, in the URL Parts field, enter https://api.peoplehr.net/Query
Click OK then click the Home tab.
Click Advanced Editor then remove all text from the box.
In the box, enter the following:
📌Note: You need to replace the text in bold with the relevant 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
Click Done then click List.Click To Table then, from the Select or enter delimiter drop-down menu, select None.
Click OK then click the Split in Columns button.
Select the relevant columns then select the Use original column name as prefix check box.
Click OK.
Access denied
When you try to follow the steps above, you may see a message that says Access Denied. This is due to one of the following:
You haven't given the API key access to the specific query.
You've entered the API key incorrectly.
The query name is incorrect in Power BI.
To check these in PeopleHR, click Settings then click API. From here, view the Query Endpoint to see if the specific query is included in the key. You can then also cross reference the query name within here to Power BI.