Chapter 39: Transforming Data with Power Query
39
command. This nifty command allows you to unpivot by selecting the columns you want to
remain static and telling Power Query to unpivot all other columns.For instance, Figure 39.21 demonstrates that instead of selecting the month columns, you
can select the Market and Product Description columns, right-click, and then select Unpivot
Other Columns.FIGURE 39.21
Use Unpivot Other Columns when the number of matrix columns will be variable.Now it doesn’t matter how many new month columns are added or removed each month.
Your query will always unpivot the correct columns.It’s a good idea always to use the Unpivot Other Columns option. Even if you don’t anticipate new matrix columns, the
Unpivot Other Columns option offers more flexibility for those unexpected changes in data.
Pivoting columns
If you find that you need to transform your data from a tabular layout to a matrix-style
layout, you can use the Pivot Column command.Simply select the columns that will make up the header labels and values for the new
matrix columns and then select the Pivot Column command from the Transform Ribbon tab.
Figure 39.22 illustrates an example.