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.