# Part 2: Power Query Unpivot Cross Tab Table with 2 Column Variables. An Easier Way! EMT 1916 Part 02 ## Метаданные - **Канал:** excelisfun - **YouTube:** https://www.youtube.com/watch?v=SYafCAdCgpI ## Содержание ### [0:00](https://www.youtube.com/watch?v=SYafCAdCgpI) Segment 1 (00:00 - 05:00) Welcome to Excel Magic Trick 1916 part two. And in this video we're going to see how to go from bad data crosstabulated table which is hard to analyze and we're going to transform it into a proper data set which is easy to analyze. Imagine trying to sort these numbers biggest to smallest and across tab. Well, I don't know how to do that without transforming it. But if you have a proper table, it is easy. Sort pivot tables too. Hard to create from something like this. Easy from a proper data set. Now, guess what? I just filmed Excel magic trick 1916 part one. And below in the comments, our pal Roger said, "Hey, there's an easier way. " He and Gert both concurred. No need to transpose twice or merge or split. So, in this video, we're going to see our awesome teammate solution for how to go from a crosstabulated table with two variables at the top of each column, one at the head of the row into a proper data set. Now, if you like what you see in this video, hey, on the PQ bookshet, 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, on the sheet 16, Seattle measure, here's our cross tab table. And just like last video, we have to convert this to an Excel table before we can import it into Power Query. So I select a single cell, use a keyboard control T. Very importantly, we want to uncheck my table as headers because we want column one, column two, the default headers to show up because we need these two variables in the first two rows. Click okay. There it is. Column one, two, three, and so on. Click in a single cell data. Get and transform from table range. Or you can use the right click key G or just rightclick get data. But I'm using the keyboard rightclick key G. Now here's a good lesson. I forgot to name the table. Table six. That is a terrible name. Everything in Excel, whether it's a worksheet. I'm going to discard an Excel table or many other named objects, you always want to name them smartly. Then it's easier to tell what's going on. I called it bad data crosstab and enter. Right click G. Now I'm immediately going to name it bad data cross tab into table and enter. I see the name here. We have a single step. If you have changed type, you want to X that out. Sometimes default settings will add that automatically. We don't want that. And over here on the left, I can see the list of queries. There's a bunch of them because answers in here, but that's the one we're working with. Now, step one, we want to take these first two rows, transpose the whole table, so they become columns. transform transpose and sure enough there's our variable one and two into new columns. Now, as we talked about last video, we want to rename each step in the query so it doesn't contain spaces. Now, this is not an advanced M code video, but let's go to home advanced editor. And this is where you can see all the coding that Power Query writes for you as you click click through the user interface. And this coding is called M code. The M stands for data mashup. Look at that. Pound double quote double quote. That's the syntax because we have a space. But look at this source. That doesn't have a space. So we don't have all that annoying syntax. Close. Click F2. And at least remove the space. Enter. Now when we click advanced editor, that is so much more polite. Done. Now last video we merged, then transpose, then brought them up as column headers, unpivot, and then split. But guess what? We have two columns right here. All we need to do to unpivot is move this variable week up to column headers. So in the upper left of the table, click the drop down and we'll select use first row as headers. And sure enough, we have our weak variable here. And we need to name these. Doubleclick column one, cargo type, and enter. Double click column two. If anyone knows a keyboard to go from naming a column to another column ### [5:00](https://www.youtube.com/watch?v=SYafCAdCgpI&t=300s) Segment 2 (05:00 - 09:00) in Power Query editor, put it in the comments cuz I don't know that one. Year and enter. Now, all we have to do is select these two columns and unpivot. So, week is a column and the volume numbers are a column. also select cargo type, hold shift, click, right click, unpivot other columns, and bam. That's so much easier than what I did last video. Luckily, we're on an awesome online Excel team. Now, we definitely want good names and table. onpivot other columns gives us an option. So, up in the formula bar, I can doubleclick. This will be weak. Double click. We'll call it volume and enter. Now, if we look in the volume column, click the filter. And this is a great trick. You can always check out a unique list. This works in the worksheet, too. And PowerBI load more. And there we have a unique list. And sure enough, down at the bottom in the volume column, which we need to add a number data type, but there's different types of data, so that won't work. Now what we're going to do is we're going to notice cyber attack outage. We need those records in a single table and then all of the volume records in a separate table. So two tables. Cancel. We're going to save this as our base query. There it is. Now we can simply reference. Rightclick reference. And remember reference is like a cell reference in Excel. It's just pointing to this query. Whatever changes here, those changes will be reflected in this query. I definitely want to rename it. You can rename it on the right or the left. F2. Cyber attack records. Now we just filter drop-down load more. And for cyber attack records, I'm unselecting everything and selecting just cyber attack. Click okay. Now the M code up here, table. select rows. Notice it says volume equals cyber attack outage. We'll keep that in mind when we make our second report. Now I want to click the first column, hold shift, click the last one, transform, detect data types. I'm making sure I have the right data types. Now after renaming all the steps for the second report, let's not rightclick reference the base. Let's rightclick duplicate the report we already have. That way we can just change the filtering criteria. Duplicate. This will actually duplicate the M code. If we click on source, we can see it's still referencing the original query. Filter to get cyber attack records. Well, I'm going to F2 to rename this step and put out instead of to get. Enter. Now we look up into the formula bar. Table. select rows. There's the source for each row. What are we looking for? Not equal but not. So we use less than greater than. That is the syntax for the logical test. Not. When I hit enter, bam, there it is. Now we need to change the data type for volume. It doesn't have a data type. I'm selecting add data types. Click the ABC. That means this is currently text, but I want it as a whole number. When I click, it's going to be polite. Do you want to replace? Yes, I do. Replace current. And now we have the correct data type. Now we have one, two, three queries that we have not loaded. And I want to load the cargo table and cyber attack table on a new worksheet. But I want this as a connection only. But I'm going to cheat. I'm going to come up to home, close, and load. And if I click this, it'll load all three on new worksheets. So click. And sure enough, one, two. Those are the tables we want. I do not want this loaded on a new worksheet. And I do not want to come over and change the load location. So I'm going to cheat. I'm going to rightclick, delete. And when I delete it, it will delete the sheet and convert. When I click delete, the load location to connection only. And now we have what we want. table one, table two for any analysis we want. And we have an awesome online Excel team. Thanks, Roger and Ge. And we'll see you next Excel magic trick. --- *Источник: https://ekstraktznaniy.ru/video/44600*