Choosing an appropriate data source is the first critical step to using Power Query. In this video, learn how to select a dataset to apply in Power Query.
- Okay, it's probably stating the obvious that before using power query or power pivot, we need data. Now there's an ever growing number of methods and data sources that are available to use in Excel. If we click on data here and then click on the dropdown next to get data, you can see the vast range of options that we have to select, but to make it easier to understand the concepts of power query and power pivot, I'm actually just going to go ahead and select from file and from tech slash CSV. And I'm going to open this local CSV file called EV sales for King County. You'll find this file in your exercise file pack. Now this file contains data from the Washington State Department of Licensing on electric vehicle sales since 2010. Now to make it a little bit easier, I edited this file to only retain data for the King County. Let's go ahead and select the data and then click on import. Now there's quite a fair bit of data here, so it may take a little while to open up the preview window. Well, you might be lucky and it appears in just a couple of moments. We have some default options at the very top here, we can then choose to transform the data before we even import it, but we can always come back to this later on. So I tend to just go ahead and click on load. Now there's roughly 150,000 rows of data that's been imported. So we may just need to be patient at this stage. And as you can see in just a couple of moments, the data has been included in our Excel file. If we click on the data menu option here and then click on existing connections, you'll see that the connection has been identified in Excel. And at some stage down the track, we'll be able to update these data and it will automatically flow through into our workbook. Let's go ahead and click on cancel. And the last thing I tend to do at this stage is give the worksheet a more meaningful name, such as say, data and let's hit enter on the keyboard. And just like that you've connected to a data source using power query. You are now ready to start doing some pretty cool stuff with your data.
- Preparing queries
- Cleansing data with Power Query
- Enhancing queries
- Creating a data model in Power Pivot
- Building relationships
- Analyzing data with PivotTables and PivotCharts
- Using Power Query with PowerPivot