From the course: Excel: Power Query (Get & Transform)

Query data from a table or range

In order to work with data in Power Query, we have to get it into Power Query somehow, and there are a lot of ways. Let me show you a few. I'm going to go to the Data tab, I'm going to go to Get Data, and From File, we have From Excel Workbook, we've got From Text/CSV. We can even pull in from a PDF. We have databases, online services, other sources. So there's SharePoint. And here is where we have our merge and append options. But primarily, we are going to use From Table/Range. Here we have these inventories of paints. And we have our data from the store. Then over on this right side, we have our Warehouse1 inventory. Let's get them into Power Query. Cursor is in the dataset. Right-click, Get Data from Table/Range. The marching ants are encompassing our entire database and that's critical. When we do this, we don't want any empty rows or empty columns. We need to clear those out or highlight the entire range, blank rows and columns, everything, and then import it all into Power Query. But right now, we have all of our data. The data does have headers, and I'm going to select "Okay." Now, something important that I want to show you. We have our data. But now to keep organized, I'm not going to leave that as Table2. I'm going to call this StoreInventory. And notice the query has been renamed StoreInventory. Now, the data is in Power Query ready for my analysis or some cleansing, whatever I need. So I'm going to close and load this. And notice, here's the query result. It's in a table. And notice that our source data is now in a table automatically. Now, I'm going to slide over, cursor in this data set. I'm in the Home tab. I'm going to format this as a table. I am going to grab this nice orange. My entire dataset has been selected, table does have headers. Okay. I'm going to get rid of the filter buttons and over here too, go back. Now, I'm in the Table Design tab. I'm going to call this Warehouse1. That's the name of the table. Cursor in the dataset, right-click, Get Data from Table/Range. Or we can go to the Data tab and select here, From Table/Range. Here's the data available to us. And notice, because we've named the table, Power Query has adopted that name for our queries. See, Warehouse1 over in Query Settings, Warehouse1 over in the left panel. And that's how you get your data into Power Query by using From Table/Range. You're going to hear that a lot in this course.

Contents