Wide" data into a "Long" database format

Unpivot_Toolkit.lambda

/*
Name: UNPIVOT_PLUS	
Description: Given a table or a range with headers, Transforms "Wide" data into a "Long" database format.	
 V4 UPDATE: "pad_blanks_with" feature to replace empty grid cells (prevents Pivot Table type-errors).
 Automatically handles merged cells (Fill-Down or Fill-Right logic). 	
 optionally removing Grid blank entries.	
 optionally removing Grid errors.	
Made By: Medohh2120
*/

UNPIVOT_PLUS = LAMBDA(table, [no_of_cols], [no_of_headers], [attribute_names], [value_name], [remove_errors], [remove_blanks],[pad_blanks_with], [pad_errors_with], 
    LET(
        /* 1. Defaults: with shadowing pattern */
        no_of_headers, IF(ISOMITTED(no_of_headers), 1, no_of_headers),
        no_of_cols, IF(ISOMITTED(no_of_cols), 1, no_of_cols),
        attribute_names, IF(ISOMITTED(attribute_names), na(), attribute_names), //Missing inputs default to na(): resolved later to 'Attribute.n' via IFERROR. 
        value_name, IF(ISOMITTED(value_name), "Value", value_name),
        remove_blanks, IF(ISOMITTED(remove_blanks), TRUE, remove_blanks), // This removes empty cells and strings.
        remove_errors, IF(ISOMITTED(remove_errors), FALSE, remove_errors), // Defaults to False to prevent silently ignoring errors.
        pad_blanks_with,IF(ISOMITTED(pad_blanks_with), "", pad_blanks_with), // Replaces both empty cells and empty strings.
        
        /* 2. Data Extraction */
        Grid, DROP(table, no_of_headers, no_of_cols),
        Anchors, IF(no_of_cols > 0, DROP(TAKE(table, , no_of_cols), no_of_headers), ""),  // Handles zero anchors, guards against -ve values.
        raw_hdrs, TAKE(DROP(table, , no_of_cols), no_of_headers),
        anchor_names, IF(no_of_cols > 0, INDEX(table, no_of_headers, SEQUENCE(, no_of_cols)), ""),
        
        /* 3. Helper Values & Grid Cleaning */
        r, ROW(Grid),
        c, COLUMN(Grid),
        Grid_No_Blanks,IF(grid="",pad_blanks_with,grid),  // This catches empty cells and strings.
            //This skips IFERROR to leave original errors intact.
        Clean_grid, IF(  
            ISOMITTED(pad_errors_with),
            Grid_No_Blanks,
            IFERROR(Grid_No_Blanks, pad_errors_with) 
        ),
        f_Grid, TOCOL(Clean_grid),
        
        /* 4. (Only if no_of_cols > 0) This loops through each anchor column: applies the SCAN (fill-down) logic then broadcasts it */
        expanded_anchors, IF(no_of_cols = 0,"",
            DROP(
                REDUCE("",SEQUENCE(no_of_cols),
                    LAMBDA(acc, anchor_idx,
                        LET(
                            anchor, CHOOSECOLS(Anchors,anchor_idx),
                            filled_col, SCAN("", anchor, LAMBDA(prev, curr, IF(curr = "", prev, curr))),
                            HSTACK(acc, TOCOL(IF(c, filled_col)))  //broadcasting core logic
                        )  
                    )
                ),,1
            )
        ),
        
        /* 5. This loops through each header row: applies the SCAN (fill-right) logic then broadcasts it (Always runs) */
        expanded_hdrs_w_labels, DROP(
            REDUCE("",SEQUENCE(no_of_headers),
                LAMBDA(acc, hdr_idx,
                    LET(
                        hdr,CHOOSEROWS(raw_hdrs,hdr_idx),
                        filled_hdrs, SCAN("", hdr, LAMBDA(prev, curr, IF(curr = "", prev, curr))),
                        user_input, INDEX(attribute_names, hdr_idx), //process each Input
                        attr_label,IFERROR(user_input, "Attribute." & hdr_idx), // Default value
                        HSTACK(acc, VSTACK(attr_label, TOCOL(IF(r, filled_hdrs)))) //broadcasting core logic
                    )
                )
            ),,1            
        ),
        
        /* 6. Smart Stack */
        header_and_grid, HSTACK(expanded_hdrs_w_labels, VSTACK(value_name, f_Grid)),
        stacked, IF(no_of_cols = 0, header_and_grid, HSTACK(VSTACK(anchor_names, expanded_anchors), header_and_grid)),
        
        /* 7. handle all four states (Blanks, Errors, Both, None) */
        FILTER(stacked, 
            VSTACK(1,  //1 Forces header retention.
             NOT(
                 (remove_blanks * IFERROR(f_Grid="", FALSE)) // Safely traps empty cells,strings and errors.
                  +
                 (remove_errors * ISERROR(f_Grid))
                )
            )
        )
    )
);

/* 
Name: SPLIT_INJECT
Description: A slightly altered version of REPLACE_INDICES. 
 Splits target columns by a delimiter and expands them horizontally. 
 If multiple indices are supplied, each target column is split independently.
 Non-target columns remain perfectly stable in their original positions.
 Optional "pad_with" replaces errors in the final result.
Made By: Medohh2120 
*/

SPLIT_INJECT = LAMBDA(array, target_indices, delimiter, [ignore_empty], [match_mode], [pad_with],
    REDUCE(array,SORT(TOCOL(target_indices), , -1),
        LAMBDA(arry, nxt, //starting from the right, the indices for the columns to the left remain perfectly stable.
            LET(
                Cols, COLUMNS(arry),
                Left, IF(nxt = 1, "", TAKE(arry, , nxt - 1)), //if split 1st column, there is no Left piece.
                Right, IF(nxt = Cols, "", DROP(arry, , nxt)), //if split last column, there is no right piece.
                ColToUnpack, CHOOSECOLS(arry, nxt),
                pack, TEXTSPLIT_COLUMN(ColToUnpack, delimiter,ignore_empty,match_mode,pad_with),
                IF(
                    nxt = 1,
                    HSTACK(pack, Right),
                    IF(nxt = Cols, HSTACK(Left, pack), HSTACK(Left, pack, Right))
                )
            )
        )
    )
);


/* 
Name: TEXTSPLIT_COLUMN
Description: Splits a 1D column of text by a delimiter and expands it horizontally.
 Optional "pad_with" replaces errors in the final result.
Made By: Medohh2120 
*/ 

TEXTSPLIT_COLUMN = LAMBDA(column, delimiter,[ignore_empty], [match_mode], [pad_with],
    LET(
        Width, MAX(MAP(column, LAMBDA(r, COLUMNS(TEXTSPLIT(r, delimiter,, ignore_empty, match_mode))))),
        /* If omitted, Excel treats pad_with as 0. ISOMITTED forces it to an error. */
        safe_pad, IF(ISOMITTED(pad_with), na(), pad_with), 
        DROP(
            REDUCE("",SEQUENCE(Width),
                LAMBDA(acc, word_idx,
                    LET(
                        /* Create a single column for this word index */
                        NewCol, MAP(column, LAMBDA(cell, INDEX(TEXTSPLIT(cell, delimiter, , ignore_empty, match_mode), word_idx))),
                        HSTACK(acc, IFERROR(NewCol,safe_pad)) 
                    )
                )
            ),,1
        )
    )
);