I'm not sure how this would be worded, but if I have the following table in Excel:
Is there a way to combine the type columns into one column to get this table:
Basically how could I have Excel create a row for each object column to type column combination? The actual table I have would have a lot more rows and wouldn't be so easy to just do this manually.
23 Answers
What you are trying to do is to Unpivot or Melt. There are three ways I can think of to do this:
- Create an OFFSET formula.
- Use PowerQuery within the Power BI engine as Lee did.
- Create a dynamic array, which I will show below.
Each has pros and cons. The Offset has the advantage of giving an instantaneous answer as soon as you enter data. On the down side, you have to copy the formula to the destination, so if the size of your input data changes, you have to copy the formula accordingly. Also, OFFSET is a volatile function, which may or may not impact performance on your workbook.
The Power Query method requires you to Refresh Data each time the data are changed.
Dynamic Arrays are like offset in that they calculate immediately, but they have the advantage of reshaping themselves as the data change and they are non-volatile. They have a disadvantage in that they will dynamically write the results into the worksheet themselves, so if you have any occupied cells in the way, the formula will SPILL.
That said, here is a Dynamic Array approach using LET to make it easier to understand, modify and debug.
=LET( unPivMatrix, A2:A4, byMatrix, B2:D4, upC, COLUMNS( unPivMatrix ), upCells, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) * upC, upSeq, SEQUENCE( upCells,, 0 ), byC, COLUMNS( byMatrix ), mux, INDEX( unPivMatrix, upSeq/upC + 1, MOD( upSeq, upC ) + 1 ), demux, IFERROR( INDEX( byMatrix, IFERROR( INT( SEQUENCE( upCells, byC,0 )/byC/upC ) + 1, MOD( upSeq, upC ) + 1 ), SEQUENCE( 1, byC + 1 ) ), mux ), demux )The unPivMatrix are the columns you want to distribute - so these would be the Types in your example. The byMatrix are the columns you want to unpivot them by - these would be Objects in your example. Think about a Pivot Table in excel. The byMatrix are the rows that you are pivoting the data by and the unPivMatrix are the data that you want to unpivot.
This formula first creates a shape for the unpivot, so that it can be multiplexed into a single column (called mux) like this:
| mux |
|---|
| a |
| b |
| c |
| e |
| f |
| g |
| ... |
The result is put into demux which first creates an array of the byMatrix that repeats the byMatrix column values for each column in the unPivMatrix
| demux col1 |
|---|
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 3 |
By referencing one more column than what actually exists in byMatrix, the INDEX function throws an error.
| demux col1 | extra column |
|---|---|
| 1 | #REF! |
| 1 | #REF! |
| 1 | #REF! |
| 2 | #REF! |
| 2 | #REF! |
| 2 | #REF! |
| 3 | #REF! |
This error is exploited to effectively fuse the two arrays (demux of byMatrix and mux) together. By wrapping the result in an IFERROR, we are able to replace the #REF! error with the values from mux.
| demux col1 | extra column |
|---|---|
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | d |
| 2 | e |
| 2 | f |
| 3 | g |
This formula is now a general purpose unpivot based on dynamic arrays. i.e. you can put any range into unPivMatrix and byMatrix and get the result you want in much the way that UNPIVOT works in Power Query.
You can try to solve it with formulas, see example below
I added some formulas text to make it a bit more clear. This approach is a bit clumsy, in my view - you would need to extend the formulas if you put more data.
A good alternative would be to do unpivoting via Power Query. There are many articles on internet how to do it try this from microsoft
Try to use Power Query to get the result:
- Go to Data- From Table/Range:
- Select Type1, Type2, Type3 column- under Transform tab- Select Unpivoted Columns- Remove Attribute column- change Value to Type- Close & Load:
