Power Query Unpivot with 2 Column Variables? Bad Data Into Good Data. Excel Magic Trick 1916

Welcome to Exom Magic Trick 1916. And in this video, we have some bad data from the Seattle airport. And we need to take this cross tab table with two variables at the top of each column and one at the head of each row and convert it into a proper data set. — Now, if you like what you see in this video, hey, on the PQ booksheet, this is the Power Query book I wrote, The Transformative Magic of Mode and Power Query Excel and PowerBI. Hey, here are the links to buy the book, either PDF or book, directly from Mr. Excel, the publisher. Now people keep data in all sorts of crazy ways. Cross tab tables are common. But you cannot analyze a cross tab table as easily as you can analyze a proper data set. Now here's the situation. 1 2 3 four different records are in the first row of this table. For record number one in the week column we should see week one cargo type column TSA screen volume. And in the year we should see 2025. Then we see that number. Then similarly for this number right here 1 2 three different variables all in the same record. Now doing this with power query is easy. Now in this workbook we're on the sheet 1916 Seattle measures. We have this data in an Excel sheet. In order to use power query we have to convert this to an Excel table. then import it up into Power Query and make our transformation. Now, next video I'll show you how to deal with a CSV file when you're importing. Not only that, but we'll see how to import a bunch of files all at once and make the transformations across multiple files. But in this video, we just want to see the basics of how to take cross tab two variables at the head of the column, one variable row and convert it into a proper data set. Now I'm going to click in a single cell and use the keyboard controll. Now very importantly [clears throat] when we have two variables like this I want to say my table does not have headers. That means it will add column one, column two automatic headers. Click okay. And that's what we want both variables in the first row. So we can eventually convert those to two different columns. Now I'm going to name this table design properties cross tab bad data and enter. Now we can bring it up into Power Query data from table range or we can use the rightclick key if you have it on your computer. Click right click or you can use your mouse and check that out right there. If we click G, rightclick G is the keyboard to bring an Excel table into the Power Query editor. So let's try it. Right click G. We can see our table with column one, column two in the power query editor. On the left, I see our query. These are solutions. Over on the right, I want to rename. We'll rename it into cross tab into table. Now, we have the source step. If your computer has changed type, which depends on the option settings, then exit out. And the first step we're going to do is we're going to take the whole table and transpose it. That way these two rows become columns. Transform. And there it is. Table transpose. I want to rename each step. And this is not a detailed M code video, but if we go look home advanced editor. Now, the advanced editor shows you all the code behind the scenes that Power Query writes for you every time you click in the user interface. If you have spaces, you get the syntax pound double quote double quote, which is annoying to look at. Notice this step does not have spaces, so there's none of that extra syntax. Close. Select step. F2 to rename. I'm going to call it transpose01. Enter. And just so we can see much better. Cancel. Now that we have both cargo type and year in two different columns, we can join them. And then when we transpose back, we'll push that up to column headers. And then we can unpivot.

So I select column one, column two, rightclick, merge columns. Separator. I definitely want custom. Let's do a dash. And I'll name the column something like merge cargo type and year. Click okay. And now we have that as a single column. Rename each step. Now I always rename them so I can actually look at the names over here and figure out what's going on. All right. Now we need to go transform and transpose back. Rename it something like transpose O2. Now look at that. We have two columns, two variables in a single cell. We could push it up so they become headers. Upper left corner of the table. I click and use first row as headers. Now I definitely want to name this column. Doubleclick and we'll call it week and enter. Now based on this new week column we can unpivot. That means now this number will be associated with two columns week one and this merged item here. This number will have week one and that merged item. So we select week rightclick unpivot other columns. And there's two columns. As soon as we split this then we'll have the three columns we need matched up with each number. Now I want to rename value. Instead of double clicking like we did for week and adding a new step, I want to just come up to the formula bar, double click value, and call this volume and enter. We're not going to rename this column right now because we're going to split it. And then we'll rename it. Rightclick column header down to split column over to by delimiter. There's our dash. At each occurrence, all of that is fine. Click okay. Now we can come up into the formula bar expand and I definitely want to not add a new step to rename but just use the existing code attribute one cargo type attribute two will be year and enter. Now we're not going to add data types yet and the reason why is this volume column has text and numbers. Now here's a great trick. Click the filter dropdown and you can always see unique list of items in the filter list. But let's click load more so we see everything. Scroll all the way down and sure enough some of the volumes have cyber attack outage instead. And we want two tables. One that shows cyber attack and one that shows the volumes. So I'm going to click cancel, save this as the base, reference it twice, and create the final tables. So, rightclick reference. Reference is just like a reference in Excel when you use equal cell reference. Here, we're just saying, hey, go get that query. Whatever changes here will show up in this query. I'm going to rename this, and you can rename it on the right or the left. F2. I'm going to call it cargo records. Come over to the dropdown. Load more. Scroll down. Uncheck cyber attack. Click okay. Now I can click volume, hold shift, click week, come up to transform, detect data types. Make sure they got the correct data types. And they did. I renamed the steps. That table's ready to go. Right click, reference, F2, cyber attack records. Come over here. And this time we want to uncheck everything. Scroll all the way down. And we only want cyber attack. Click okay. Select volume. Shift weak. Detect data types. Now I have three queries and I do not want to load this one. And there's a few ways I can do this. But check this out. I'm going to sort of cheat. I'm just clicking the button. That means it will load all three as new worksheets with these names. So I'm going to click and there they are. I want cyber attack records, cargo records. But this one, instead of coming over and changing the load location and then deleting this, watch this. I'm just going to rightclick, delete the sheet. That'll delete the sheet and convert the load to connection only. Delete. Connection only. That's exactly what I want. And then I have one, two queries. So in this video we saw how to go from a cross tab into a proper data set using Power Query and then load both proper tables to the worksheet. All right, next video we're going to have more fun with similarly structured bad data, but we're going to have a bunch of CSV files and we'll see how to transform and convert all the files into a single table. All

right, we'll see you next Excel magic trick. —