-
-
Save ExcelRobot/54fe4bf04f24a83b83d3b3bddda4d3c3 to your computer and use it in GitHub Desktop.
| /* | |
| Name: Unpivot Table (UNPIVOT) | |
| Description: Given a table range with headers and array of header names, unpivots the | |
| specified columns in place, optionally removing any blank entries. | |
| Written By: Excel Robot (@ExcelRobot) | |
| Category: Array | |
| */ | |
| UNPIVOT=LAMBDA(table,[columns_to_unpivot],[attribute_name],[value_name],[remove_blanks], LET( | |
| _ColumnsToUnpivot, IF( | |
| ISOMITTED(columns_to_unpivot), | |
| DROP(TAKE(table, 1), , 1), | |
| columns_to_unpivot | |
| ), | |
| _AttributeLabel, IF(ISOMITTED(attribute_name), "Attribute", attribute_name), | |
| _ValueLabel, IF(ISOMITTED(value_name), "Value", value_name), | |
| _FirstColumnToUnpivot, MATCH(INDEX(_ColumnsToUnpivot, , 1), INDEX(table, 1, ), 0), | |
| _UnpivotColumnCount, COLUMNS(_ColumnsToUnpivot), | |
| _ColumnNumbers, SEQUENCE(1, COLUMNS(table)), | |
| _IncludeColumns, (_ColumnNumbers >= _FirstColumnToUnpivot) | |
| * (_ColumnNumbers < _FirstColumnToUnpivot + _UnpivotColumnCount), | |
| _UnpivotColumns, FILTER(_ColumnNumbers, _IncludeColumns), | |
| _OtherColumns, FILTER(_ColumnNumbers, NOT(_IncludeColumns)), | |
| _FullOuterJoin, CROSSJOIN( | |
| CHOOSECOLS(table, _OtherColumns), | |
| VSTACK(_AttributeLabel, TRANSPOSE(_ColumnsToUnpivot)), | |
| TRUE | |
| ), | |
| _WithValues, HSTACK( | |
| _FullOuterJoin, | |
| VSTACK(_ValueLabel, TOCOL(DROP(CHOOSECOLS(table, _UnpivotColumns), 1))) | |
| ), | |
| _RemoveBlanks, IF( | |
| OR(ISOMITTED(remove_blanks), remove_blanks), | |
| FILTER(_WithValues, INDEX(_WithValues, , COLUMNS(_WithValues)) <> ""), | |
| IF(_WithValues = "", "", _WithValues) | |
| ), | |
| _ColumnOrder, LET( | |
| n, COLUMNS(_RemoveBlanks), | |
| s, SEQUENCE(1, n), | |
| IFS( | |
| s < _FirstColumnToUnpivot, s, | |
| s < _FirstColumnToUnpivot + 2, s + n - _FirstColumnToUnpivot - 1, | |
| TRUE, s - 2 | |
| ) | |
| ), | |
| _Result, CHOOSECOLS(_RemoveBlanks, _ColumnOrder), | |
| _Result | |
| )); | |
| /* | |
| Name: Cross Join Tables (CROSSJOIN) | |
| Description: Given two tables, returns the Cartesian product of rows from the tables. | |
| Written By: Excel Robot (@ExcelRobot) | |
| Category: Array | |
| */ | |
| CROSSJOIN = LAMBDA(table1, table2, [has_headers], LET( | |
| _HasHeaders, IF(ISOMITTED(has_headers), TRUE, has_headers), | |
| _Data1, IF(_HasHeaders, DROP(table1,1), table1), | |
| _Data2, IF(_HasHeaders, DROP(table2,1), table2), | |
| _D1Rows, ROWS(_Data1), | |
| _D1Cols, COLUMNS(_Data1), | |
| _D2Rows, ROWS(_Data2), | |
| _D2Cols, COLUMNS(_Data2), | |
| _OuterJoinedData, MAKEARRAY(_D1Rows * _D2Rows, _D1Cols + _D2Cols,LAMBDA(i, j, | |
| IF(j <= _D1Cols, INDEX(_Data1, ROUNDUP(i / _D2Rows, 0), j), INDEX(_Data2, MOD(i - 1, _D2Rows) + 1, j - _D1Cols)))), | |
| _WithHeader, IF(_HasHeaders, VSTACK(HSTACK(TAKE(table1, 1), TAKE(table2, 1)), _OuterJoinedData), _OuterJoinedData), | |
| _WithHeader | |
| )); |
I'm wondering if there's an example of using this anywhere. I was able to successfully set up Unpivot as a LAMBDA function, but when I try to use it I get various errors. I assumed that usage was UnPivot(Array, Name of Column) but I continue to get N/A or other errors. I can't seem to figure out the right combination of parameters.
I'm wondering if there's an example of using this anywhere. I was able to successfully set up Unpivot as a LAMBDA function, but when I try to use it I get various errors. I assumed that usage was UnPivot(Array, Name of Column) but I continue to get N/A or other errors. I can't seem to figure out the right combination of parameters.
You betcha, here you go:
This looks great and I look forward to using them. It took me a while to figure out that it uses HSTACK And VSTACK and these functions haven't been released yet https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c
Actually, I'm not on the Beta so I don't have HSTACK or VSTACK either. That's why I wrote my own as LAMBDA functions. You can use them too!
Just import this Gist: Array Manipulation LAMBDA Functions
I'm wondering if there's an example of using this anywhere. I was able to successfully set up Unpivot as a LAMBDA function, but when I try to use it I get various errors. I assumed that usage was UnPivot(Array, Name of Column) but I continue to get N/A or other errors. I can't seem to figure out the right combination of parameters.
You betcha, here you go:
Thanks so much. I get it now. I was not parameterizing the columns correctly in the call.
This is great! Now I don't need to go to Power Query for an unpivot.
Could you write an "PIVOT" function?
The columns_to_unpivot parameter is now optional. So, when you have a range to unpivot that has only one column of keys on the left and you want to unpivot all the columns to the right, you no longer have to specify which columns.
it's not working, when column header more than two or more
it's not working, when column header more than two or more
True, it's only expecting a one row header at the top of your table. Thanks for clarifying that!
This looks great and I look forward to using them. It took me a while to figure out that it uses HSTACK And VSTACK and these functions haven't been released yet https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c