The final approach to reorganizing data that we'll look at is pivoting data across columns. In Excel pivot generally types along with pivot table. The pivot column command works very much like a pivot table expansion can. And alternatively, it also comes along the unpicked column command, which does as the name implies and reverses that pivot. This probably doesn't make any sense. So let's look at a scenario and we'll start with the unpinning.

First. The example you see here is a very simple report. It has a few numbers such as revenue, profit, cost of goods sold and debt figures listed for each of the Year End periods. For quick comparison. You've probably seen a report like this somewhere that compares one set of numbers against another set over time, even if it was looking at your own personal expenditures month by month for evaluating your budget. This type of layout is great for comparing it information, but it's not very good for using that information.

Table based information such as this is much easier to form into alternative reports once it's been formatted as a list based source. So let's turn this report into a data list. manually. You can do this by copying and pasting the name dros and then copying and pasting the date next to each of these names. And finally, you'll copy and paste all of the values next to that. Once you've done this for the first date, you need to repeat it for each of the other dates into a vertical list to arrange all of the data together.

Now my data has already been arranged into a table and it has table column names. And so all I need to do is turn this into a data query. To recreate what we just did manually. We have two different commands. The first approach consists of selecting each of the three columns that would be 12 31 2016 through 1231 2018. And then under our transform tab, I can select this on pivot columns drop down, and it'll pick and pivot my information.

This is turned my header columns into the attribute field, which has each of the dates. And then the values from each of those columns has been placed into the value column. But as with most commands, this isn't our only approach. Let's go ahead and delete that step. And we can redo it with our alternative option. instead of selecting each of the fields that I want to unpick it, I want to select the fields that I don't want to unpick, but in this case, that's simply the data Name field.

Upon selecting that field, I can go to the transform tab again. And now to make sure to select the drop down and I'm going to select on pivot other columns. This generates the exact same result as before. While these unpaved commands convert data into a log style of list format. The pivot command takes data in a list format such as this and converts it in the other direction. So let's go consider a different data set.

Here we have a list of stores, their fruits and their carried quantities. It's a small table, so you can quickly look and see which stores have which fruits just by scanning it. But let's create a pivot anyways, to assist our base table have a store column, and within the store column, it'll have one entry for each of our store locations. Now, I want to pivot on the fruit column. So what that means is I'm going to take each of the fruit names and turn it into its own unique column. So instead of having a fruit column, I'm going to have a banana column, an apple column and an avocado column.

As part of this pivot, I want to use the Quantity field to populate these fields. So I'm going to distribute the Quantity field based upon where it falls in the table. That means that in our banana column for entry 0554, I'll enter six for our apple column for store 554. I'll enter four, and I won't enter an avocado entry since 554 doesn't have an avocado. I'll do this for the other stores. And we can see how the fruits are distributed by the stores after having pivoted on our fruit column.

We've generated a two dimensional view of our quantity values across store and fruit. Notice how any fruits that did not have a store entry are empty. In a data set this small, it's very easy to pick apart the stores that are missing fruits. But what if our data set was significantly larger? this data set isn't much larger, it only has 75 entries, so it's far from a real world situation. Yet, there's enough information here to make it difficult to identify which stores have inventory for fruit and which ones don't Making it a prime target for a column pivot.

Before we pivot, I've highlighted a few rows here. And notice that we have two entries for key leaves for store number 00 to one, and doing this manually, or even with improper Excel formulas, we might miss this. In fact, there may be other duplicates in this data set that I'm unaware of. With that in mind, let's go through the process using the get and transform tool and see what options we have to handle these duplications. And since the data is already in a table, I'll simply select the cell and I'll go and load it into a table using the from table command. The pivot column command is on the transform ribbon directly above our and pivot column command.

Before I press it, I'm going to select the fruits field and then click on it to get a new dialog box. The box asks me to select the values column to use in order to create the columns. This can include any one of the other fields. For our case, we want to use the Quantity field to populate the various fruit fields. Now remember our issue with the duplicate entries, we could have done a group by to eliminate the duplication. However, that functionality is baked into the advanced options right here in our pivot column.

By clicking on the Advanced Options, I can see a list of aggregation commands that we can use. For a number field. This will default to the sub command, which will add up each of the quantities into a total. Within the drop down. There is a list of different options. We could count the non blank rows count all the rows find the maximum or minimum values or even run averages are find the median.

For now I'm going to stick to some and I'll press OK. Very quickly we see the results. Since this is fairly hard to read, I'm going to load it into a table here we can quickly and easily identify which stores have and do not have a given fruit. Dragon fruits, for example, are only available in one store. Kiwis, only available in two. But now perhaps from Not interested strictly in the quantity of fruit, but how many entries each store has in their inventory system.

So let's go ahead at the query, I'll click on the pivot column gear. And under Advanced Options, I'll change some to count all and press OK. Now I'll close and load it again. And now as I suspected earlier, there are quite a few duplicate entries. Lemons, oranges, and pears all have duplicate entries for stores, 1742, and 2132. Additionally, we have the keys for 00 to one and lemons for 00 to one. With this inventory system, I happen to know that we need to take the minimum value rather than the sum, we can quickly revise our approach to do that as easily as going back into our query editor, modifying the pivot column, going to advanced options and selecting minimum and pressing OK and closing and loading the query again.

This gives us the report that we had desired. Now before we close this lesson out. I want to cover Two quick commands that I've passed by that I probably should have covered earlier, the duplication and reference commands. Both can be valuable for creating more complex queries. And both are quite simple. Right clicking on any query in the query pane will bring up your menu that has a duplicate.

And a reference command to duplicate creates an exact copy of your query so that you could create a duplicate, change your pivot, and then if you so desire to append the two together. The other command is the reference command. The reference command creates a new query that uses the reference query as the data source. This is sort of like the duplication command, except it creates a new query from the end of the previous query. So any changes you make to the previous query will impact this query. As I've created a reference to this query, if I change my pivot and make it the count all again, when I refreshed this referenced query, it updates to mimic the previous one References can be useful for building query trees where you have one base query that then is used to build other queries.

With this, I've now concluded the second chapter on generally reorganizing data, it's time to put each of these pieces to work in the next exercise. For this exercise, I've created a series of datasets. In addition to a sample of how the final output should look. This exercise is significantly more challenging than the first one, but it will force you to use all of the tools that we've covered in this chapter. To create a query as a part of a management dashboard. Each of the data tables have been defined for you and created as queries.

Your task is to take the report shown here and turn it into a live functioning query. Upon doing so, the operating month table and store number tables will allow you to toggle the values and then update the report to see live changes. The reference and duplication features will be very handy in this section. Besides for creating different query branches, and then later linking them back together as needed, with merges and depends, give the exercise your best shot. And if you get stuck the last video associated with this chapter, we'll walk through the entire solution. Good luck.