Power BI Tutorial | From Flat File To Data Model

yo sup it's Patrick from guy in a cube and in this video I'm gonna convert a flat file to a full data model using power bi desktop stay tuned okay so converting a flat file to data model why well you ever open up a pivot table or a power bi desktop file and you have this long list of columns and it's just complete chaos how do I know what to do how do I know which column I need right there's no there's no organization to it and so I've seen this a lot out in the while and I just wanted to step back kind of go back to the basics and show you how to take this flat file that maybe you requested it from IT and they just seen you this CSV file or this excel file with all these columns you just load it up and you start building your reports but whenever I create something in power bi I'm thinking about self service I want somebody to use this I want somebody to build their own reports because I hate creating reports just kidding just kidding I kid I kid that kid but right when I do create reports I want to easily go after the columns that I want to create I want to easily find them and then so I'm building my reports quickly as opposed to searching through this very long list of columns understand what I'm saying long lists don't want the long list when I bring a little structure to it so you guys know how I like to do right instead of all this talking let's head over to my laptop okay so the first thing I'm gonna do is I've reached out to IT and I have some hey I need some sales data and of course they send me a nice little flat file so I'm gonna go get my little CSV file so I'm going to choose CSV and browse to where they save that CSV file format click open after I click open power bi is gonna do this phenomenal thing here get the data for me and I'm gonna click Edit don't just click load don't just click load click edit cuz we're gonna massage it we're gonna shape it we're gonna get intimate with the data all right so we go ahead and click Edit see here query editor will open and the first thing I do when it loads up is I go after the columns that I don't want remove them let's clean this guy up I will admit my this flat file was a little oversimplified you may need to do some things you know you may need to take a little more action where I can become a little more intimate with your data to kind of identify your primary keys for your queries and things like that mine is somewhat simplified but the first step is I go and look for the columns I need to remove and so you may need to you know you may have to pick and choose and go through this a great way to do it is to go to choose columns and just uncheck the ones you want you know like that or if they're in order my mind just happened to be in order you can click the first one go all the way to the end press down shift and just select remove columns so I don't need any of those I knew that right I knew that before right it's like a cooking show I kind of prepared for this right then the next thing you want to do is write I really spend some time looking at this data trying to figure out how's my data models gonna look you know what's gonna be like where's got my additive values gonna be kind of like a fact table in the data warehouse if you're familiar with that concept and then I look for how I'm gonna a granade a group my data like for an example in this data I have product I see there's a product key and there's also a sales territory key somewhere in here I remember sometimes I head over to my whiteboard and I actually kind of architect the model although I grab a piece of paper or use my my wonderful surface book with this inking capabilities to draw that out either way right I identify what I need and then I'll go ahead and duplicate the query a couple of times so we're gonna do that a couple of times don't worry I'm not bloating it out because when I click close and apply the only data that's going to be loaded is the data that's left in the query editor so for this one for my sales I'm gonna go ahead and call this my my sales orders all right that's my sales orders that's what my sells is gonna happen and what I'm gonna do is kind of clean it up the only thing I really need are the keys that I'm gonna relate to those other queries that I'm gonna create from the duplication of that the initial query so I'm gonna go ahead and remove everything you know that's not necessary so like all this product detail stuff right I'm gonna remove and then pulling the sales territory all I need is the key so I'm gonna remove all of that stuff also right so all I need is this the keys from each one so go ahead remove that so now I'm left with just my additive values and my date and then my keys to the other queries and then I'm gonna come here and this is gonna become my product query right and so what I'm gonna do is I like this a lot I'm going to select just the product stuff I think that's all I need and power query or the query editor has a great feature where you can say remove other columns for some of you this may be a rehash of something that you've done before it's like all Patrick this is so simple but I'm taking it back to the basics because I want people to understand that you don't need you know some formalized data warehouse process they have a nice data model in power bi you can use the query editor to create your own data model all right so now I have this I'm gonna go ahead and call that to my product and then I'm gonna repeat the exact same steps up here I'm gonna call this my sales territory all right and then I'm gonna remove everything but my sales territory stuff okay and I need to there's a couple of things out here on the end that I need to remove too and I'll have to repeat that over on the other one that's all right yes and go here perfect and so I forgot a step and so the next thing you want to do you want to ensure that those you know what you're gonna group or aggregate by my case product and sales territory they're unique it's a unique listing of each row in these queries so I'm gonna select all the columns right click and remove duplicates so that gives me a distinct list and repeat the exact same steps for my sales territory duplicates so now I have a distinct list of everything I need this is great this is perfect now I have a nice I have my orders well my additive values are and I have my two dimensions right if again if you're familiar with data warehousing terminology I have these two tables that's what I'm gonna add gate all this stuff back right so now I'm gonna go ahead and click close and apply this is where the magic happens right it's taking all my data building you know the semantic layer writing into memory and then it's actually gonna create this wonderful data model for me I'm so excited I'm actually starting to perspire a little bit okay so if you click on relationships you can see what Part B is done is it's detected those key columns because they have the same name and they're the same data type and it's established those relationships for me so you know you can see the formation right the formation of this nice clean data model from this little baby flat file well not a little baby flat file like Norma's flat file so instead of scrolling up and down that field list looking for columns I've kind of group them together in query so now it's easy for me or whoever I decide to share this model with they can you know easily go hey the product informations in the product table the sales Terri story sucks the sales Terri sales territory stuff is in the sales territory table right and if I want to I can't have another table so I'm gonna do this just because I want to do it right let me create another table here oh not this way or this old-school right I'm gonna use modeling and say new table and what I can do here is I'm gonna call this my date my date table equals my first shift enter to give me a little space in my magic bar my calendar right equals to calendar auto go check out what calendar Auto does I think Adam has a video on what calendar Auto does right press return and then shift enter and what I'm gonna do right here is say at columns then I'm gonna use my calendar and then I'm gonna say this is gonna be my year all right there's a column that it's already created out there for me it's gonna be my month month sort right and I'm gonna say but and then finally I'm kind of go ahead and add know what name then I'm gonna say format right you guys know what I'm doing with everybody you guys know this this is a piece of cake give me a couple of M's here and finally there's my you know my month table and then I go over to my relationships and I'm gonna establish a relationship between date and/or today and then I'm gonna do one final thing over here it's marked this as a date table just give me a second it's like my date column and now I have a full data model where I can start doing you know my reporting from if it's me right if I'm building this I'm actually going to take this publish it up to power bi com publish my data model to power bi calm and then open up another version of power bi desktop and choose get my power bi data set and use this right here is my data model so if I have to make any changes whatever I make changes to this one and then I use power bi and another you know another instance of it running purely as a visualization tool so I separate the data modeling and the visualizations and hope that makes sense right if not I have a video on data silos where you can go and check it out where I kind of dig into that a little more all right so you can start building your reports and doing everything you want guess this nice clean pretty data model all right how you guys doing it today for you guys that don't have data warehouses are you leveraging this are you taking a different approach are you doing some of it and acts I'd like to know right post it in the comments below if this is your first time visiting the guy in the cube channel go ahead and subscribe if you like my video give me two big thumbs up as always from Adam and Patrick thanks for watching we'll see you in the next video

Channel: Guy in a Cube

Views: 123,642

Keywords: Power BI Tutorial from flat file to data model, Power BI Tutorial | From Flat File to Data Model+, from flat file to data model, power bi tutorial, power bi tutorial for beginners, flat file, power bi, power bi data model tutorial, power bi data modeling, power bi desktop, power bi desktop tutorial for beginners, power bi desktop tutorial video, power bi get data, data, tutorial, data modeling, data modeling concepts, data modeling tutorial, power pivot, get & transform

Id: vjBprojOCzU

Length: 10min 27sec (627 seconds)

Published: Wed Jun 13 2018