Transform a matrix into a table with three columns using techniques like reverse pivot, unpivot, flatten, and normalize

To update your source table, you can click on the Power Query result table sheet and select “Refresh All” under the Data ribbon. If you want to convert an Excel matrix into a table, you can use the “reverse pivot”, “unpivot”, or “flatten” method. One solution for this is to follow the steps for Excel 2003.


Question:

My task is to transform the Excel matrix with the code

FIRST

into a table with the code

LATER

.


FIRST

:

    P1  P2  P3  P4
F1  X
F2  X   X
F3      X       X
F4      X   X


LATER

:

F   P   VALUE
F1  P1  X
F1  P2
F1  P3
F1  P4
F2  P1  X
F2  P2  X
F2  P3
F2  P4
F3  P1
F3  P2  X
F3  P3
F3  P4  X
F4  P1
F4  P2  X
F4  P3  X
F4  P4


Solution 1:

To “reverse pivot”, “unpivot” or “flatten”:

  1. To create a PivotTable and PivotChart Report in Excel 2003, select any cell in your summary table and then choose the Data option.

    <p>
    <img alt=”SO20541905 first example” class=”lazyload object-cover” data-src=”https://i.stack.imgur.com/JEIMk.gif” loading=”lazy”/>
    </p>

To access the Wizard in later versions, use the key combination of Alt + D and then P.

To access the desired feature in Excel for Mac 2011, simply press the keyboard shortcut consisting of the Command key, the Alt key, and the letter P altogether. A visual representation of the shortcut can be found by referring to the provided link.

  1. Choose numerous consolidation ranges and proceed to the next step.

    <p>
    <img alt=”SO20541905 second example” class=”lazyload object-cover” data-src=”https://i.stack.imgur.com/cDWJB.gif” loading=”lazy”/>
    </p>

  2. Select the option “I will create the page fields” under “Step 2a of 3” and proceed by clicking on “Next”.

    <p>
    <img alt=”SO20541905 third example” class=”lazyload object-cover” data-src=”https://i.stack.imgur.com/GNIfN.gif” loading=”lazy”/>
    </p>

  3. When you reach “Step 2b of 3”, enter the range for your summary table (for instance, A1:E5 for the sample data) in the designated field called “Range”. Then, proceed to click “Add” followed by “Next”.

    <p>
    <img alt=”SO20541905 fourth example” class=”lazyload object-cover” data-src=”https://i.stack.imgur.com/AXvW2.gif” loading=”lazy”/>
    </p>

  4. For the <a class=”text-blue-600″ href=”https://in4any.com/how-to-change-the-name-of-a-pivot-table-field-and-avoid-error-pivot-table-field-name-already-exists” title=’How to change the name of a pivot table field and avoid error “Pivot Table field name already exists”‘>pivot table</a> in “Step 3 of 3”, you can choose any location as the existing sheet will suffice since the PT is only needed temporarily.

    <p>
    <img alt=”SO20541905 fifth example” class=”lazyload object-cover” data-src=”https://i.stack.imgur.com/ZE66T.gif” loading=”lazy”/>
    </p>

  5. Click the “Finish” button to generate the pivot table.

    <p>
    <img alt=”SO20541905 sixth example” class=”lazyload object-cover” data-src=”https://i.stack.imgur.com/1GQj1.gif” loading=”lazy”/>
    </p>

  6. Double-click on the point of intersection for the Grand Totals, which can be found at Cell V7 or identified as <code>
    7
    </code>.

    <p>
    <img alt=”SO20541905 seventh example” class=”lazyload object-cover” data-src=”https://i.stack.imgur.com/wofg2.gif” loading=”lazy”/>
    </p>

  7. <p>
    The PT may now be deleted.
    </p>

  8. To transform the Table into a regular cell array, simply choose the Convert to Range option from the Quick Menu that pops up when you right-click on the Table.

Launch Excel has a video that discusses the same subject matter and is of exceptional quality, in my opinion.


Solution 2:


PowerQuery, a free Excel add-in available for download at http://www.microsoft.com/en-us/download/details.aspx?id=39379, presents an alternative method for unpivoting data that does not require the use of VBA.

First, you need to install and activate the Power Query add-in, and after that, proceed with the given instructions.

Assign a title to a column in your data source and transform it into an Excel Table either by selecting Insert followed by Table or pressing Ctrl-T.

Click on “From Table” located on the Power Query ribbon after selecting a cell in the table.

The Power Query Editor window will display the table once it is opened.

Select the first column by clicking on its header. Next, access the “Unpivot Columns” option from the “Transform” ribbon and choose “Unpivot other columns” from the drop-down menu.

If the “Unpivot other columns” command is not available in your Power Query version, you can still achieve the same result by selecting all columns except the first one (by Shift-clicking on
column headers
) and using the “Unpivot” command.

Upon executing the command, a table with a level surface will be generated. To proceed further, navigate to the Home ribbon and click on the option labeled as “Close and Load”. Consequently, the amassed data will be automatically transferred to a new sheet in the Excel document.

Moving on to the exciting segment, include new information into your origin table, such as…

Navigate to the sheet containing the Power Query result table and select the “Refresh all” option from the Data ribbon. A notification similar to the following will appear:

Power Query has the capacity to be used multiple times, and it can also be connected to data that changes dynamically.


Solution 3:


Instead of relying solely on VBA, PowerQuery, and other one-time solutions, consider utilizing INDEX(MATCH(…)) to create a more dynamic table that can be updated regularly.


Solution 4:

With the incorporation of the LET feature and
dynamic arrays
, it becomes possible to achieve this resolution without using VBA.

=LET(data,B2:E5,
     dataRows,ROWS(data),
     dataCols,COLUMNS(data),
     rowHeaders,OFFSET(data,0,-1,dataRows,1),
     colHeaders,OFFSET(data,-1,0,1,dataCols),
     dataIndex,SEQUENCE(dataRows*dataCols),
     rowIndex,MOD(dataIndex-1,dataRows)+1,
     colIndex,INT((dataIndex-1)/dataRows)+1,
     dataColumn, IF(INDEX(data,rowIndex,colIndex)="","",INDEX(data,rowIndex,colIndex)),
     unfiltered, CHOOSE({1,2,3},INDEX(rowHeaders,rowIndex),INDEX(colHeaders,colIndex), dataColumn),
     filtered, FILTER(unfiltered, dataColumn<>""),
     unfiltered)

To display all items, including those without data, the last parameter should not be modified. However, if you wish to remove the empty fields, set the final parameter to “filtered”.

Frequently Asked Questions