digest
Welcome to Power Query video number five. If you want to download this workbook and the CSV files, click on the link below the video. Now, CSV files in the Power Query section: when you click the link below the video for number five, all the source videos are going to be the same ones we used in video number four.
All right, so here's the setup. Notice that in the last video we imported these all in one step, but if we open up one of these and look at it, we can see that there are many repeats. What I'd like to do instead of having this column with the actual city name is have a City ID, and then a lookup table with 0, 1, 2, 3, 4, 5 and then the list of the cities. That way, when we dump it into Power Pivot, we can create a relationship between the two tables and save on file size.
Now here's the cool thing: I want to actually build the lookup table and put the index number into this city. We'll change this to a City ID column, all in the query. The idea is that we're going to have our folders and our files here, and I took the other three out. We're going to start with three. Then when we add new files here and refresh through Power Query and Power Pivot, the query that we build will build the lookup Dimension table for us and automatically update when we add new files.
All right, I'm going to close this and start here. Here's our file. Let's start by importing and creating our lookup or Dimension table. Power Query, From File, From Folder, Browse, City Data, click OK, click OK. Now here are all the columns, and for this one we only want to use the file name. So I'm going to right-click and Remove Other Columns.
Now let's split this. I'll first use a delimiter, and we'll say Custom, and I'm going to use that dash. Click OK. Now I'm going to right-click and Remove. Now select Name 2 split delimiter. This time, Custom, but we'll use a period. Right-click and Remove. Double-click, and I'm going to call this City. Enter. Add a column, Add Index Column, move it in front, double-click, and we'll call this City ID. Enter.
Now we can close and load to. We're only going to create a connection. We'll load to the data model later. Load. Oh, I forgot to name this. Edit, D City table, enter, close and load.
Now let's go get all our data: From File, From Folder, Browse, click OK, click OK. Now all we want is our Content. I'm going to right-click and Remove Other Columns. Click the double arrow and check each column to see the data type. It looks like Date, looks like Dest, and this one I'm going to change to Text. I'm going to do the same trick we did last time: City, Date, Sales, Store ID for the first table. That worked, but there are a couple of records below with these names repeated as a record, and we want to get rid of them. So I'm going to filter them. There's a way that in our next video we'll use a column method, which can work because this time we can only get to all of them since there are not very many different items in this column. But if there are too many, you actually can't use this method. For us, this will work. I'm going to say Not City, and that's what this filter is. As we saw last video, the query is being written as Not Filter. We could go look at the Advanced Editor, and there are all the steps done. Home, Close and Load To.
Let's name this first. I'm calling this Imported Tables. Enter, close and load to, connection only, only create connection, load.
Now I want to merge these tables. It's not really a merge; we're going to create a relationship between the two tables. We'll start with Imported Tables. Notice there's a City column, and then down here, D City table. Now I'm going to select both of them, and now there's a relationship. We have 60 or 600,000 matching rows. I'm going to click OK, and we'll get this table column, which is in essence the relationship between the two columns. I don't need this City anymore because it's going to be replaced by an index number. So I'm going to remove this, come over here and expand, OK. I don't need this column. Right-click, Remove. I'm going to double-click and name this City ID. Now this column will allow us to link through a relationship over in Power Pivot.
I am going to call this F All Data. Enter. Now I can close and load to, and I'm sending this only as a connection over to the data model in Power Pivot. I'm going to click Load.
All right, that one is loaded. Now I'm going to right-click, Load To, Load to Data Model, and Load. Now I'm going to close and go over to Power Pivot, Manage, or Alt+B+M, and there are our two tables. Now we can do a little bit of maintenance here. I'm going to format that as a date. Click in the top cell for Sales and add some currency. There's our table, and when we refresh and there are new files over there, this thing will just be populated. It'll be quite amazing.
Let's go over to Diagram View. Here's our fact table, here's our lookup or Dimension table, City ID, boom. Now let's go back to Data View. There's our City table, there's our fact table. Let's create a pivot table: click PivotTable, click OK. There it is on a new sheet. Now I'm going to pull Store ID down to Rows, and Sales down to Values. Notice that the formatting flowed from our column when we formatted it.
Now we're going to insert a slicer. All, and I'm going to say from City. Click OK. Now if we filter this, it's following the rules of Power Pivot. When I click Oakland over in Power Pivot, we can't really see it, but it filters this table. We can look over in Diagram View: this table is filtered, which then filters this table. So this one is filtered down to Oakland, and through the relationship this table is filtered. Power Pivot doesn't have to chug through as many rows when it's doing this calculation based on one or two conditions.
Now let's save this and go add some files to that folder. I'm going to right-click, Paste, or Control+V. There are two new files. You're kidding. So San Francisco and Tacoma should be added to the lookup table, or Dimension table, as three and four. Let's go see: Data, Refresh All. And you've got to be kidding me, look at that. It totally showed up: Tacoma, San Francisco. If we go over to Power Pivot, we can look in our City table and check that out. Oh my heavens, is Power Query the most amazing thing ever?
All right. We first started in Power Query, From File, From Folder, and did a little querying. If we look at our queries right here, there are our queries. Then we sent them over to Power Pivot, built a relationship, and built our pivot table.
All right, we'll see you next time.