Use Power Query to import data for Python in Excel

What is Power Query?  

Power Query is a data transformation and preparation tool designed to help you shape your data, and it’s available in Excel and other Microsoft products.

Use Power Query with Python in Excel to import external data into Excel and then analyze that data with Python. 

To learn more about Power Query, see Power Query for Excel Help. 

How to use Power Query to import data for Python in Excel 

Take the following steps to learn how to import a sample external data set and to use with Python in Excel.

1. To see the Get & Transform Data and Queries & Connections groups, select the Data tab. Choose Get Data to select your data source. You can import data from multiple sources, including comma-separated values (CSV) files. Every import creates a query.
   
   This example imports data from the Northwind OData service, a test data source. To do this, select Get Data > From Other Sources > From OData Feed.

2. Next, enter the following link to the Northwind OData service and select OK.

   https://services.odata.org/northwind/northwind.svc/

3. In the Power Query preview dialog, select a data table to load into Excel. For this example, select the Categories table, and then select the arrow next to the Load button and choose Load To.... The following screenshot shows the Power Query preview dialog with the Categories table selected and the Load To... option displayed.
   
   

   Notes: 

   • You can also select Transform Data from the Power Query preview dialog. This allows you to edit the data in the Power Query Editor before importing it into Excel.

   • If you want to view the data on the Excel grid, select the Load button. This will load the selected table directly to the Excel grid.

4. Select Only Create Connection to establish the data connection from the source. Power Query automatically creates a query for this connection.
   
   

5. The Queries & Connections pane opens automatically, listing the query created by Power Query for the selected data source—in this case, the Categories table from the Northwind OData sample dataset. To manually open the Queries & Connections pane, on the Data tab select Queries & Connections.

   Tip: Hover over the queries in the task pane to get a preview of the data.

The next section describes how to analyze the Categories data with Python in Excel.

Use Power Query data with Python in Excel

The following procedure assumes that you’ve created a Power Query connection with the Categories data from the Northwind OData service described in the preceding article section. These steps show how to analyze the Categories data with Python in Excel.

1. To work with external data with Python in Excel, enable Python in a cell by entering the =PY function. Next, use the Python in Excel xl() formula to reference Excel elements, like a Power Query query. For this example, enter xl("Categories") into the Python in Excel cell.

2. The Python cell now displays the Categories data within a DataFrame, as returned by the xl("Categories") formula. Select the card icon next to the DataFrame to view a preview of the data in the DataFrame. The following screenshot shows the DataFrame preview.
   
   
    

   Tip: Resize the DataFrame dialog using the icon in the bottom right corner.

3. Convert the data in the DataFrame to Excel values, which returns the data to the Excel grid. To convert the DataFrame to Excel values, select the cell containing the DataFrame, and then select the Insert Data icon. The following screenshot shows an example of this icon next to the DataFrame.
   
   
   
   Selecting the Insert Data icon opens a menu with a list of values relevant to your data.
   
   
    

4. Select the first value in the menu, arrayPreview. This value displays the Python data from the DataFrame in the Excel grid. See the following screenshot for an example.
   
   

5. Now that you’ve used Power Query to import external data and processed that data with Python in Excel, you’re ready to start analyzing data with Python in Excel. To learn how to create Python plots and charts with your data, see Create Python in Excel plots and charts.

Related articles

• About Power Query in Excel

• Create Python in Excel plots and charts