Excel Power Query #05: Create Dimension Table From Fact Table for PowerPivot During CSV File Import

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 alright so here's the setup notice 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's many repeats what I'd like to do instead of having this column with the actual city name I want city ID and then a lookup table with like 0 1 2 3 4 5 and then the list of the cities that way when we dump it in to PowerPivot 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 city ID column all in the query the idea is we're going to have our folders or files here and I took the other 3 out we're going to start with 3 then when we add new files here and refresh through power query and PowerPivot 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 we're going to start 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's all the columns and for this one we only want to use the file name so I'm going to right click 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 okay now I'm going to right click remove now select name to split delimiter this time custom but we'll use a period right click remove double click I'm going to call this city enter add a column add index column move it in front double click we'll call this city ID enter now we can go and I'm going to close in load two we're only going to create a connection will load to the data model later and 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 remove other columns click the double arrow check each column to see the data type it looks like date looks like decimal and this one I'm going to change to text I'm going to do our same trick we did last time city date sales store ID for the first table at work but there's a couple 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 in our next video we'll use a column method which can work because this time we can only get to all of them because there's not very many different items in this column but if there's too many you actually can't use this method but for us this will work I'm going to say not City that's what this filter is as we saw last video the queries being written as not filter we could go look at our advanced editor there's all the steps done home close and low to let's name this first I'm calling this imported tables and enter close and low to connection only only create connection load now I want to merge these table now it's not really a merge we're going to grab 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 6000 600,000 match 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 so CD ID now this column will allow us to link through a relationship over in PowerPivot I am going to call this F all data enter now I can close and load too and I'm sending this only as a connection over to the data model in PowerPivot I'm going to click load alright so that one is loaded now I'm going to right click load to low to data model and load now I'm going to close and go over to power pivot manage or alts BM and there's 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 add some currency there's our table and when we refresh and there's new files over there this thing will just be populated it'll be quite amazing let's go / - diagram view here's our fact table here's our lookup or dimension table city ID oh 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 pivot table click OK there it is on a new sheet now I'm going to pull door ID down to rows 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 so when I click Oakland over in PowerPivot we can't really see it but it filters this table we can look over in diagram views this table is filtered which then filters this table so this one will be filtered down to Oakland and then through the relationship this table is filtered so power pivot doesn't have to chug through as many rows when it's doing this calculation based on one 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's two new files there your can so San Francisco and Tacoma should be added as in the lookup table or dimension table as 3 & 4 so let's go see data refresh all and you gotta be kidding me look at that it totally showed up Tacoma San Francisco if we go over to powerpivot we can look in our city table and check that out Oh My heavens is power query like the most amazing thing ever all right so we first started in power quick query from file and folder we did a little query if we looked at our query right here there's our queries then we send it over to power pivot built a relationship and built our pivot table all right we'll see you next trip

Channel: ExcelIsFun

Views: 38,578

Rating: 4.9408865 out of 5

Keywords: Excel Power Query, Excel 2010, Excel 2013, Mike Girvin, Michael Girvin, Mike excelisfun Girvin, excelisfun, Highline College, Data Analysis, Power BI, Microsoft Power BI, Import Data, Transform Data, Merge Data, Create Dimension Table From Fact Table for PowerPivot During CSV File Import, From Folder, CSV File Import, Load tables to Data Model in PowerPivot, PivotTAble, Slicer, Create Relationship, Add New Files to Folder

Id: UhV9VBA0A_g

Length: 9min 1sec (541 seconds)

Published: Fri Nov 28 2014