What is Power Query?

Power Query (formerly Get & Transform) is a tool built into several Microsoft Office applications that allows users to extract, transform, and load data from external sources (such as an API) and import it directly into these applications.

It can be particularly useful in scenarios where users want to ingest API data into a CSV, but don’t have the technical resources or ability to write code. 

Power Query is available in Microsoft 365 on both Windows and Mac. Learn more →

Importing data from an API

Here are the steps to take API data in a JSON format, and import it directly into Excel via PowerQuery. As an example, we’ll be fetching historical app ratings from Sensor Tower's API.

1. Navigate to our API documentation page and copy the request URL after inputting your desired parameters:

   https://api.sensortower.com/v1/ios/review/get_ratings?app_id=284882215&start_date=2020-12-01&end_date=2021-1-1&country=US&auth_token={auth_token_here}

2. Open Microsoft Excel 365 and create a blank workbook. Then in the Data tab, select Get Data from Web.
3. Select the Basic option and paste the request URL. Click OK.
   
4. An Access Web content window will appear. In the Anonymous section, select https://api.sensortower.com/ from the dropdown and click Connect.
   
5. The Power Query editor window will open. This is where you can edit the imported data before it gets loaded into the spreadsheet. In the top-left, click To Table to convert the data into a table.
   
6. Excel will prompt you to create a table from a list of values. You can leave these options as default. Hit OK.
   
7. In the first column (Column1), click the button on the right of the header and select the column headers you would like the spreadsheet to have.
   It is recommended to untick Use original column name as prefix as this will use the key names from the API. Select OK.
   
8. Now, you should see familiar data (such as app_id, country, date, breakdown) populated into the table. However, you might notice the breakdown column still says List. This is because the breakdown key in our API contains an array of values (1-5 star app ratings).
   
9. Click on the two-arrow button and select Extract Values. Pick the Comma delimiter from the dropdown and hit OK.
   
10. Now, you should see the nested values appear on the table. However, it would be ideal if each value had their own column, since we’re splitting by star rating.
    
11. Right-click the breakdown column and select Split Column > By Delimiter.
    
12. A Split Column by Delimiter window will appear. Simply leave the options as-is and hit OK.
    
13. Now your data is finally formatted properly and each value has their own separate column!
    

    You may also rename the column headers to something more readable.

    
14. When you are happy with the format of the column headers, press the Close & Load button at the top-left to load the data into a spreadsheet.
    

You’re done! If you ever want to go back and edit some steps, you may right-click the query in the Queries & Connections pane and select Edit. The Applied Steps section will show all the steps you’ve carried out in the Power Query editor.

This is just one example of what you can do with Power Query, but if you have any questions, please don't hesitate to reach out to us at support@sensortower.com.