Setup info Sheet
In Excel create a new workbook and rename Sheet1 to set up
Add 3 headers to the sheet starting with A1
site
apiKey
Endpoint
In the Ribbon Bar select Forums > Name Manager and select New
Create 3 named ranges the cells under the headers we created in step 2 site is pictured below for examples and what you should have when you create the 3 names ranges.
Fill in the cells with information so we can create the Query to pull the API. For this example well use the employee's endpoint
site: app.innergy.com
apiKey: <replace with your key>
endPoint: employees
Now that we have our setup done, we can jump into Power Query
Power Query
In the Ribbon bar select Data > Get Data > Launch Power Query Editor
Now that we are in Power Query, we need to create a function to get the values of the name ranges that we created in the setup sheet
Click the Get data drop down and select Blank "Query".
You will get the script editor for the blank query. We will turn this "Query" into a "Function". To do this you need to define a variable as something and pass that to the let that shows in line one.
(rangeName as text ) => let
The source of the information it needs to query our named ranges that we are passing in the first line.
The full function would be like this:
(rangeName as text) => let Source = Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1] inSource
Then click next at the bottom right of the window and will get a fx Query1
We want to rename this to getValue
Now we need to create a query to get the employee using the API
Click and select Get data drop down and black query again to get the editor to open for a new query
This time we want to leave the "let" alone and only change the Source. For this e need to tell the Source that it is getting a Json Document from the a Web Contents using a url and headers for our API Key. To do this use the string below:
Source = Json.Document(Web.Contents("https://" & getValue("site") & "/api/" & getValue("endPoint"), [Headers=[#"Api-Key"=getValue("apiKey")]]))
You will see in the url we constructed it with the getValue function to get the value of the site, endPoint, and apiKey named ranges.
Select the next at the bottom right to move to the next step
You will get a list return to you that each row shows [Record] we are going to expand that from here.
Select the Column Header "List" and then right click for the context menu to select convert to table
On the Convert to table popup just click ok as this step was done for us as its already a list of records from a Json Document from our Source
You will now have a Table that shows each cell having a [Record] like the list did but now we have a new button next to the Column1 header title of opposing arrows. Select this button to get the expand record/list popup, make sure the check box is unchecked and then click OK
Now we can see the Roles Column has a [List] in it. Select the opposing arrows in that column header to expand it.
Rename the Query1 to say Employees by right clicking on the name and selecting Rename then Type Employees and hit Enter on your Keyboard.
Last Step is to Close & Load this Query to its own sheet. At the Top left select the Close & Load button
SUCCESS!!!
You now have pulled all of the Employees from your tenant and have them displayed in an Excel Sheet!! If you make a change in INNERGY then just elect the Refresh button to get the new data from the API endpoint.