 welcome back to another video in our Power BI series. So the last video we showed you how to download the Power BI application and a very brief introduction and getting started and pulling in your first piece of data. And this is exactly where we pick off in this video. So you can see we've got our simple piece of data here. So we've got the employee ID, employee name, but this is only one table out of three available to us in our Excel file. And again links to that file is in the description to this video or any data that we refer to in this video you can find the link in the description so you can obviously create it and follow along at home. If you look in the actual Excel file you can see there's actually three pieces of information or three tabs. So the first one is employees. You can see simple two-column data set that you've already gotten to Power BI here. Then also got a sales tab so you can see we've got an employee ID, the location that employee is based in and the number of sales that they did on a particular date. So these are just the days in the last year or we've actually got some future dates so please just ignore that. We shouldn't have been the case but hopefully it just proves or works as an example of purpose. And then we've got other locations table as well. So what we want to do is we want to pull all three of these data sources into Power BI and then we want to join them based on their relevant information. So at the moment how that would work is you can see in our sales table we've literally just got an employee ID and a location ID and some sales data. What we want to do is join the sales, the simple sales data to the employees data so we can actually have the employee name rather than ID here and likewise for location rather than having a location ID we would like to display the city name. So hopefully that makes sense. So what we're going to do is pull that into Power BI and show you how to join based on those IDs. So we're basically starting to create a relative database in our Power BI. So just move that Excel to the side. So what we need to do is just repeat the steps we looked at in our last video and go into get data and just go into Excel. Actually what you can do is you can just put this Excel button here I believe it in the last video but just to make it more simple because it just follows or flows. Get data, Excel and then obviously it's opened up for me at the actual path where that file is stored. If I just now click company sales once again and go open and then once it thinks about it it'll then open up our navigator. So we've already got the employees information so we don't need to click that but we're going to just click both the locations so you can see that looks very much the same as what we just brought up in the sheet and we're also going to open up sales as well. So let's click that. So you can see this is a this is probably my fault something's gone wrong here for me. Again similar problem of employees it's not picking up the first the actual headers it's picking up column numbers but you can see for sales it has actually done it correctly for us so obviously something I'm doing wrong here but we'll just need to go into locations just amend that or transform that data like you did with the employees one as well. And you can see we've got a transform data button here so you can do that as you're going or installing the data. Bombs from the load both of these and then do that transformation after. So just click load and just let them think about it for a bit as it's putting the information in to the processing queries and then once it's finished processing what you about to see is we'll have another two tables available to us on this right-hand navigation side. So there we go we can see that if we now expand locations we can see the locations fields and if we expand sales we can expand the sales fields and really interesting as well because we haven't put this to this yet. The date you can see is identified as a date value in there so if we go into this drop-down you can see that we've got a date hierarchy available to us. So if we didn't want to just use that particular date so the actual day date that makes sense we can actually pull out just a year the quarter or month applicable to that date. So that become beneficial to us down the line when we're working with charts or we want to summarize the data in a different manner to what it's pulled into. So again this gives us more flexibility than potentially when we're working with data in Excel. So let's just collapse all of those. So I'm just going to go into locations and just sort out these column headers once again. So we'll go into home and we'll go transform data and you can see it's opened up on this page. We just need to make sure we navigate to the correct table so it was location so I've got locations selected here and you see this is the issue. Just use first row as headers, click that button, it does a transformation for us, close and apply and then that will be now forced into place for future iterations where we refresh the data and pull the information in as well. So we can see that looks better. So again all the employee fields here, all the location fields and the same for sales as well. So on this left-hand side and I appreciate I'm jumping around but I want to try and touch on these things as they become relevant. So the first option we have on the left here is the report. So the report tab is what we have here. It's our dashboard page where we can bring in various piece of information whether it's tables or graphs and summarize them on the page. If we go to the second one you can see this action with data. So this allows us to have a very similar view where we've got the navigation on the right-hand side which allows us to have a better look at each data set and you see sales got a bit more in it for us to look at so that we can either do things like reformatting the data or alternatively we have the ability to add calculated fields to each data set. So if you wanted to have something on the lines of let's say total sales divided by the number of units sold to achieve those sales we could do an average or a division sum here in the new column and this is the screen from which we do it. We won't be touching on that in this video but again this is another one we'll be touching on later in the series. The last one we have available to us is the model. So if we go into model you can see that these are all the tables in the fields available to us in our data model what is power BI and because we've used good structure and our employee ID is the same as our employee ID in sales so let me rephrase that. So employee ID is obviously the employee ID. If we were to call this NID in employees but over here we call it ID obviously it wouldn't be able to make the automatic connections that is already done for us therefore we need to click and drag to do that manually. So as we've done good structure as we put it in here power BI will automatically find the connections for us. If however let's just delete this one so delete that way of relationship. If we needed to do this manually or whether it's either the names are different but obviously I would encourage you to make sure that naming conventions are maintained the same across tables but if we needed to do this whatever manually all you need to do is go to our employee ID click and drag and then hover over employee ID in the sales table and you'll see it'll make that connection for you so that's how you can get around it if it hasn't already done it for you. So that is the model. So the model is really useful when I say as we get more and more tables into our power BI it allows us to visualize all these different connections between the tables available to us. Well this is the basic structure of our data as we go so a lot of sales everything basically results around the sales table. So this is where it allows us to connect to the final employee name based on the ID and also find the location or the city name again based on the location ID. So let's just go back into our dashboard so we now know the data is connected by that model sheet. So if we now wanted to pull in additional information here let's just make this table a bit bigger or what you need to do again if you're not already selecting that table you'll see nothing is allowed you're not really able to do much we just want you to put in your visualization but to update an existing visualizational table just click on that table and you'll see you then have all the variables available all the fields available to edit or add more to it. So what I want to do now is just bring into the city so let's go drag city or city. You can bring the city a location ID if you wish as well so let's just put them all in and then likewise for sales let's just bring in the employee ID, location ID and let's go total sales and total units and the only reason I've done that is just so you can see that the employee ID in both the location the sales and employees is the same. So we know the days were joined and because that is we don't need all that on there so we can get rid of both the employee IDs we can get rid of both the location IDs and there we go we have our complete dataset available to us. So we've joined three pieces or three data tables together what has enabled us to summarize our data overall so we can now see the individual the city they reside in the total number of sales that they've done and in the total unit of sales as well. So I think that I'll do for this video so you're now able to obviously install Power BI and you're able to also bring in three pieces of data or three data tables and have them join in Power BI as well. Obviously if you want to use the data we're looking at here you can get that in the links in the description below this video or if you want to use your own data obviously you're not limited to just three tables you can do this with a lot more than just three tables but I wanted to start with a smaller dataset just so everyone got the idea of how it worked. So if you enjoyed that video please do give the video a like also if this is your first time find our channel and see in our videos please don't forget to subscribe to the channel and hit that Bell notification button so you're notified of all of our future videos. Like I say we're currently looking at Power BI in this series but we've also done videos on Excel and VBA so if you haven't seen those or you would like to learn more about even one of those topics make sure again you check out the channel and those playlist available and like I say you can learn some more information about those as well. So thank you very much for watching and we'll see you in the next video.