Importing Data from an Excel File
Importing Data From an Excel File
Previous Session Summary
In the previous lesson, we looked at how to rename queries in a variety of different ways. We also learnt one of the two key ways to join datasets together; how to append queries.
Lesson Synopsis
In this lesson we’re moving on to how to connect PowerQuery in one Excel file to the data in an entirely separate Excel file. The ability to do this opens up a realm of time saving and efficiency possibilities. For example in the last few lessons we’ve extracted a PO dataset saved within the same Excel file as the PowerQuery editor, performed some basic cleaning operations and then loaded the data back into Excel. We then appended a second version of the PO dataset, also saved within the same Excel file, to the first version.
Being able to query an Excel file directly means that we don’t need to save datasets into the same workbook that we’re working in. That saves us some time. If we have an updated dataset, perhaps our original dataset had an error, then it is easy to save the updated dataset over the original, again saving some time and possibly improve accuracy.
In this lesson then we look at how to connect to our first Excel file then.
Resource Listing
The Video
Play Video
- 02:37 - How can I extract data from an Excel file?
- 03:00 - How do I select the tab where the information is stored?
- 04:00 - What happens if I select Close & Load to?
- 07:24 - How can I change the Load to options in Excel?
Lesson Notes
In this lesson we learn how to connect to an Excel file that is saved on a drive that a user has access to. This can be a OneDrive, computer hard drive or even a shared drive. In this lesson we’re going to do the same tasks as we performed in the last few lessons; connecting to PO data, but the data will be held in a separate Excel file from the one in which we perform our query.
To connect to an Excel file, we first go to the Data tab, then on the Get Data and Transform section of the ribbon, we go to Get Data, then From File and then From Workbook.
PowerQuery then asks us for a file on our drive from which we want to extract our data. We can either type the file address in or alternatively browse for the file.
Once we’ve selected the file we want, we get to a Navigator screen where PowerQuery asks us which sheet or tab in the Excel file that we’ve connected to has the data that we wish to extract into PowerQuery. PowerQuery will present us with a list of tabs on which there is data, and when we click on each tab, there is a preview of the data in the preview screen on the right hand side of the menu.
After we’ve selected the sheet that we want and pressed ok, we’re back into the PowerQuery editor. We can then transform the data in exactly the same way as we did in the previous lessons, in exactly the same way as if the data we extracted had been in the same workbook.
Once our data is transformed then we also look at another option for closing the Editor and Loading the data back into Excel. In the previous lessons we have only looked at loading the data back into Excel in the form of a formatted table. However there may be instances where you don’t wish to load the data at all into Excel. In this event, then we can select the Close and Load To button in the PowerQuery editor and then select the Only Create Connection option. This will mean that the results of the query will not load into an Excel worksheet. Nonetheless, the query still exists and can be seen in the Queries and Connections pane. If you decide that you want to change the Load To options when you’re back in Excel, then you can do that too. You simply need to right click the query that you want to amend in the Queries and Connections pane and select the Load To option from the resulting pop up menu.
.... and a little more!
Open files – An important warning: You must NOT have the file that you want to connect to open when you connect to the file for the first time. You must also not have it open when you try to refresh the data or refresh the preview in the PowerQuery Editor. The query won’t be harmed if you do have it open when trying to complete these actions; the Editor will just tell you that it’s not possible.
Using Excel files while the PowerQuery Editor is open – You might have noticed this already, but you can’t do any work in any Excel files if you have the PowerQuery Editor open. It’s a slightly irritating trait of PowerQuery, although it’s only true if you’re using PowerQuery in Excel, not PowerQuery in Power BI (we’ll get to that later).