Use Power Query to import data for Python in Excel
Python in Excel is currently in preview and is subject to change based on feedback. To use this feature, join the Microsoft 365 Insider Program and choose the Beta Channel Insider level.
Python in Excel is gradually rolling out to Excel for Windows customers using the Beta Channel. At this time, the feature is not available on other platforms.
If you encounter any issues with Python in Excel, please report them by selecting Help > Feedback in Excel.
New to Python in Excel? Start with Introduction to Python in Excel and Get started with 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.
Important: Power Query is the only way to import external data for use with Python in Excel.
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.
Tip: If you’re already familiar with Power Query and want to work with existing queries in your workbook, skip to Use Power Query data with Python in Excel later in this article.
-
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. -
Next, enter the following link to the Northwind OData service and select OK.
https://services.odata.org/northwind/northwind.svc/
-
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.
-
-
Select Only Create Connection to establish the data connection from the source. Power Query automatically creates a query for this connection.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
