Exporting PowerSteering Data into Microsoft Excel using Power Query

PowerSteering can be integrated with Microsoft Excel via Power Query. By doing so, PowerSteering data can be exported via REST API and viewed directly in Excel:

In this example, PowerSteering Tag data has been pulled into Excel using Power Query and the PowerSteering REST API.

Power Query is Microsoft's data connectivity and preparation technology. It allows users to easily access data from a variety of sources and shape it to their needs. Fortunately, it simplifies the process of accessing data via the PowerSteering API, with little to no coding required. For more information on Power Query, see the Microsoft website.

Note: Power Query is included with Excel 2016 (or any later version). If you are using Excel 2010 or 2013, you may need to download it separately. You should contact your office administrator if you currently do not have access to Power Query through your version of Excel.

This page will walk you through how to set up a simple integration between Microsoft Excel / Power Query and PowerSteering via the API. Once these two programs integrate, you can view and organize PowerSteering data directly in Excel.

Tip: This process requires some knowledge of RESTful API. If you are new to working with APIs, you may want to consult someone who has some prior experience.

To integrate PowerSteering with Excel / Power Query:

1. Select RESTful API Help from the "Help" menu:

   

2. Select the Examples button:

   

3. Select which type of data you would like to pull into Excel using the "Services" drop-down menu under "More examples".

   Note: For the sake of simplicity, "Tag Service" will be our example.

4. Specify which data you would like to pull using the "URL" drop-down menu:

   

   In the "Tag Service" example, selecting the URL that ends with "tags" will pull Tag information.

5. Select the Show button.

6. Copy the URL that appears under "JSON Request: GET":

   

7. Paste the URL in a separate browser tab and press ENTER on your keyboard.

   Note: This will display your requested data in JSON format:

   

   Tip: You should verify the "count" in the first line to ensure that your request has pulled your preferred amount of items. For example, we can see that the API call above has pulled 56 Tags:
   
   By default, PowerSteering will max out at 1000 items. If you would like to change this number, contact your PowerSteering representative.

8. Open up a "Blank Workbook" in Excel:

   

9. Select the Data tab.

10. Open the Get Data menu and select From Other Sources and then From Web:

    

11. Paste the URL from Step 6 into the "URL" field in the window that appears and select OK:

    

12. Select the Basic tab from the "Access Web Content" window that appears.

13. Enter your PowerSteering user name and password into the applicable fields.

14. Select the level directly after "...psteering.com/" from the "Select which level to apply these settings to" drop-down menu:

    

15. Select the Connect button.

16. Select the Load button.

    Note: After selecting Load, a single row will appear in Excel indicating the item count. Also, a "Queries and Connections" window will appear on the right-hand side of Excel with the query you just added:
    

17. Double-click on the query to open the Power Query Editor.

18. Right-click on the "Table" cell and select Drill Down to open up a more detailed view of your dataset.

19. (Optional) Select any of the column drop-down buttons to sort or filter the data before loading it into your Excel workbook:

    

    Tip: Visit Microsoft's Power Query site for tips on editing queries.

20. Select Close & Load:

    

After selecting Close & Load, your data will be loaded into your Excel workbook: