 What's going on everybody? Today we're continuing our Power BI tutorial series and in this video we're gonna be looking at Power Query. Now Power Query is really great because it allows you to actually transform the data before you actually get it into Power BI. So if you wanna make any changes like adding or deleting a column or changing the data type or a ton of other things, you can do all of that in Power Query. Now without further ado, let's jump onto my screen and get started with the tutorial. All right, so before we jump over to Power BI and start using Power Query, I wanted to take a look at the data and this is the Excel from our last video called Apocalypse Food Prep. And in that video we went through and we bought some rice, some beans, water, vegetables and milk all for the apocalypse, getting prepared for that. Now we decided to buy some additional things like rope, some flashlights, duct tape and a water filter, several water filters. And after we purchased those, our boss or whoever we're working with or somebody decided to go and make a pivot table. Now in this pivot table, they kind of broke it out by Costco, Target and Walmart and had all the items, had some subtotals as well as some grand totals right here. And then they decided to kind of copy and paste that into this. And you'll see this a lot when you're working with people who use Excel, they like to kind of make things like this, maybe make it into like a table or format it a little bit differently but you'll see stuff like this a lot. So this is what we're gonna actually pull into Power Query and work with. Now we're gonna imagine that this is all we have. This is the only thing we were working with and I'll kind of reference this pivot table a little bit. But we're gonna pretend this is all we have and we wanna transform it to make it a lot more usable to where we can make visualizations with it. So let's hop over to Power BI and pull this Excel in. So what we're gonna do is click import data from Excel. We're gonna click apocalypse food prep and click open. And then it's gonna bring up this window right here. Now this is where we can choose what data to bring in. So we can take a preview and just click on it real quick and this is the pivot table that we were looking at. So it does have that pivot table. So we are able to pull in just a pivot table and then we have the purchase overview where it's kind of that formatted thing that we're just looking at with all the colors. We're gonna pull both of those in. So we're gonna pull in the pivot table and the purchase overview. Now we could just load it or we could transform it and we're gonna click transform and that's gonna bring us to Power Query. So let's click on transform data. So now really quick before we actually jump into working through this and transforming it, I wanna show you what the Power Query editor looks like. So if we go right over here, we have our queries and these are the tables that we actually pulled in and we can click on those and kind of go back and forth between them. Now up top we have our ribbon and the ribbon offers a lot of functionality. We have things like remove columns, keep rows, remove rows, split columns. These are all things that we're likely to use when using this Power Query editor. There's also another tab called transform where there's a lot of functionality here as well. Things like unpivoting a column or transposing columns and rows and using a first row as a header. Some of the things that we'll be looking at today. There's also another tab called add a column and this one's pretty self-explanatory where you can add additional columns like deleting a column, creating an index column or a conditional column. Those are the three main ones. There's also view, tools and help but we're not gonna really be looking at those today. And then on the far right side, we have our query settings. You can do things like change the name. So we can call it pivot table 2022 and it'll update right over here on our query side. And we have our applied steps. Now our applied steps are extremely important and very, very useful. Anytime we make any change to transform this data it's gonna be documented right here and then we can go back and look at it or we can even delete that change in the future if we want to and go back to a previous version of what we just did. So when we loaded the data into Power BI it did a few things for us. It shows the source and navigation and it promoted the headers and then it also changed the data type. So if we wanna check we can actually see those things or change those things like this source right here. You can click on this little icon and it's gonna bring up the actual path where we got this file. So if we want it to change that or it changes in the future we can come here and we can change this file path. But we're not gonna do that right now. So let's click on cancel and let's go back down to change type. So promoted these headers and obviously these headers are not correct. We're looking at this pivot table and not the purchase overview but it changed these column headers and so in the future if we wanted to we could easily change those but it did that for us and it changed the type as well. So if you look right here it says ABC123 all the way over here it's where it just says ABC. ABC means it's only going to be text where ABC123 means it could be basically anything text or it could be numeric. So now let's go over to purchase overview and this is the one that we're actually gonna be working on the most but we might be looking at pivot table just a little bit to kind of reference it and see some of the differences. So before we do anything let's just take a look at how Power BI decided to take this data in. So it shows this apocalypse food prep overview as kind of the first column and that was kind of our header or the title of what we were looking at before and then all these other columns are basically column one, two, three, four, fives. So that's something that we're gonna want to change in just a little bit. There's also all these blank columns right here at the top and kind of these null values as we go along and we'll take a look at those and we kind of what we're gonna wanna get rid of some of this and just clean this up to make it more usable for our Power BI visualizations. This may be perfectly fine and acceptable in an Excel but when you're pulling it into Power BI the real reason you're pulling it in is to create visualizations not just it to look good in an Excel. So we're gonna need to clean this up quite a bit. So let's go right up top. The first thing that I want to do is I wanna get rid of these top rows. So we're gonna go to this top ribbon and we're gonna click remove rows and we're gonna select remove top rows and we're gonna select two because we have one, two rows of all nulls and those are completely useless. We just wanna get rid of them right away. So let's click okay and it remove those. The next thing that we want to do is this location, product and all these dates these are actually the column headers that we wanted. So what we need to do now is we wanna go over to transform and wanna say use first row as headers. And just like that we have location, products and these dates as our headers exactly how we wanted them. Now let's say for whatever reason we made a mistake and we needed to go back we would just select remove top rows and that would be perfectly fine. Now you can see over here it promoted the headers but it's also changed the data type. So before, if we went to before we remove the headers these were all ABC123, ABC123 because it had a lot of different data types in there so it just kind of made a generic data type. But when we promoted these headers the first thing that I decided to do was also change this data type for us giving us its best guess as to what this data type is and decided to do this decimal. So this one two is a decimal but we're actually gonna change that and all you have to do is click on this 1.2 or the data type that it has right here for you and we're gonna click on fixed decimal number. And let's do replace current and now it's just a little bit better. So now it's 2.70, 2.5 and that's normally how we would read values like this cause this is money. So we would normally read it to the second decimal just like that and if we have it on the second decimal for some we should probably have it on the second decimal for all of them. So really quickly I'm gonna go through and I'm just going to change that and it should be pretty quick so hang with me for just a second. All right, that is perfect. Now for the purposes of what we're about to do we don't actually need these subtotals or this Costco total, Target total and Walmart total as well as the grand total really we wanna get rid of those and so what we're going to do is we're gonna go right over here we're gonna click on this dropdown and we're gonna try to filter this data before we actually load it into Power BI. So we're gonna filter and we're gonna say remove empty and let's remove those and it's gonna take out all of those nulls. If we wanted to try to filter this out by saying something like Costco total or Target total we could do that by going right here clicking this dropdown on products go into text filters and saying does not contain and let's do insert and we're gonna say does not contain and we wanna say total and let's click okay and again it filtered out all of those things. So there's a few different options that you can do if you wanna filter out rows that contain either null values or specific values. Now the next thing that we're gonna do is actually get rid of a column, this grand total column and so what we're gonna do is we're gonna click on the very top part where it says grand total and then go back over here to home and we're gonna click on remove columns and it says insert that's because we're on this filtered rows one right here but what we're gonna do is just insert that and it'll insert it right there that's totally fine we can just move it to the bottom. Now we got rid of this column entirely. Now this looks really good visually. I like how this looks I like how everything is set up. The biggest thing about this is that when you're actually wanting to use this for visualizations these columns as dates doesn't really work too well and so what we're gonna want to do is we're gonna wanna transpose this or pivot this to where these dates are actually rows. So what we're gonna do is select the first date which is January 1st all the way through April 1st and we're gonna hit shift and click on that April 1st right there to select all of them at the same time and then we're gonna go over here to the transform tab and we're gonna click unpivot columns and let's see what this does. And so now what we've done is we've basically recreated our original Excel that we had so let's go back and take a look really quickly at that. So this looks almost identical to what we have in Power BI right now and this is extremely usable and very good for visualizations and is much, much better than this but again, we were pretending that this is what we were given at the beginning. So you have to imagine somebody just handing you this and you need to make it much more usable for visualizations in the future which happens a lot and we actually wanted to create this we just weren't given this. Now a few last things that we might want to do is we wanna clean this up just a little bit we're gonna select the data type and change this to date and then we're gonna select the value and I double clicked on the value and I actually wanna call this cost or product cost, product underscore cost. And then for the location I actually want this to be called store. So now this looks really good but I wanna show you one thing really quickly on this pivot table 2022. So let's go back here this looks very similar to how we had it when it first started. One thing I wanted to show you really quickly and I wanna click on this first one we're gonna make this our column header and then we're gonna try to pivot or unpivot this January, February, March, April. So really quickly, let's do that. So we're going to transform use first row as headers. So now we have this January, February, March, April. Now, if you notice these are not dates these are actually text it says January, February, March and April. So if we go to do this and we click unpivot and here's the columns that are created when we unpivot it it is January, February, March and April these are not dates. So we cannot go and change this to a date because that would error out because it's actually text. So it's something that you wanna look out for it's something that you need to be aware of and you can change that in the pivot table. So you wanna be aware of how it actually sits and looks in the Excel or whatever data source you're pulling from before you actually pull it into Power Query to transform. And now the very last thing that we need to do to finalize all of this is go over here to close and apply. And once we click that everything that we've worked on is gonna be applied to the actual data and it's gonna load into Power BI to create our visualizations. So let's go ahead and click on that. And so now the data has been pulled into Power BI let's go right down here to data and we can see the data right here. If we need to transform this data again we can bring it back into the Power Query Editor window by just clicking the transform data button and it's gonna bring us right back. So I hope that this was helpful. Thank you so much for watching. If you liked this video be sure to like and subscribe below and check out all my other videos and everything data analysts related. I'll see you in the next video.