Last active
May 4, 2025 08:38
-
-
Save wynhopkins/b0c1418c2b220fb2a970eb796ee4ac42 to your computer and use it in GitHub Desktop.
UNPIVOT_LAMBDA
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /*Courtesy of Access Analytic | |
| Website: https://accessanalytic.com.au/ | |
| YouTube: https://www.youtube.com/@accessanalytic | |
| Gist: https://gist.github.com/wynhopkins/2efdf218ed78be03a0a082ff6a99dad1 | |
| ------------------------------------------------------------------ | |
| ------------------------------------------------------------------ | |
| */ | |
| /*----------------------------------------------- | |
| FUNCTION NAME: aaUNPIVOT | |
| DESCRIPTION: Unpivot a range, by default first column is kept static | |
| Select Full Range including Headers | |
| ( or don't include headers and then set parameter to false) | |
| DEFAULTS: Assumes 1 column on left to be left static | |
| Assumes Headers are included | |
| Assumes Blanks are to be removed | |
| INPUTS: Option to change number of columns on left to keep static | |
| Option to state no headers by entering FALSE | |
| Option to keep blanks by entereing FALSE entered (default is remove) | |
| Option to rename Attribute Column | |
| Option to rename Values Column | |
| */ | |
| aaUNPIVOT | |
| =LAMBDA(data, [staticColsOnTheLeft], [hasHeaders], [removeBlanks], [attributeHeader], [valueHeader], | |
| LET( | |
| sCols, IF(ISOMITTED(staticColsOnTheLeft), 1, staticColsOnTheLeft), | |
| useHeaders, IF(ISOMITTED(hasHeaders), TRUE, hasHeaders), | |
| dataBody, IF(useHeaders, DROP(data, 1), data), | |
| rows, ROWS(dataBody), | |
| cols, COLUMNS(data), | |
| unpivotCols, cols - sCols, | |
| headers, | |
| IF( | |
| useHeaders, | |
| DROP(INDEX(data, 1, SEQUENCE(1, cols)), , sCols), | |
| "" | |
| ), | |
| colCount, sCols + IF(useHeaders, 2, 1), | |
| fullOutput, MAKEARRAY( | |
| rows * unpivotCols, | |
| colCount, | |
| LAMBDA(r, c, | |
| LET( | |
| dataRow, INT((r - 1) / unpivotCols) + 1, | |
| dataCol, MOD(r - 1, unpivotCols) + 1, | |
| unpivotIndex, dataCol + sCols, | |
| isStatic, c <= sCols, | |
| isAttr, useHeaders * (c = sCols + 1), | |
| isValue, c = colCount, | |
| staticVal, INDEX(dataBody, dataRow, c), | |
| attrVal, IF(useHeaders, INDEX(headers, dataCol), ""), | |
| valueVal, INDEX(dataBody, dataRow, unpivotIndex), | |
| IF(isStatic, staticVal, IF(isAttr, attrVal, valueVal)) | |
| ) | |
| ) | |
| ), | |
| cleaned, IF( | |
| ISOMITTED(removeBlanks) + (removeBlanks), | |
| FILTER(fullOutput, INDEX(fullOutput,, colCount) <> ""), | |
| fullOutput | |
| ), | |
| attrLabel, IF(ISOMITTED(attributeHeader), "Attribute", attributeHeader), | |
| valLabel, IF(ISOMITTED(valueHeader), "Values", valueHeader), | |
| result, | |
| IF( | |
| useHeaders, | |
| VSTACK( | |
| HSTACK( | |
| INDEX(data, 1, SEQUENCE(1, sCols)), | |
| attrLabel, | |
| valLabel | |
| ), | |
| cleaned | |
| ), | |
| cleaned | |
| ), | |
| result | |
| ) | |
| ) | |
| ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment