UNPIVOT

[lrobbo314]

UNPIVOT translates a set of columns in a table into attribute-value pairs, combined with the rest of the values in each row.

Based on the Power Query Table.Unpivot function. I used several lambda helper functions which will be listed below.

I will list the name of each lambda above the code for each formula.

UNPIVOT

=LAMBDA(pivot_cols,unpivot_cols,
    LET(
        pvt_rng,pivot_cols,
        upvt_rng,unpivot_cols,
        pvt_data,DROP(pvt_rng,1),
        upvt_data,DROP(upvt_rng,1),
        pvt_head,TAKE(pvt_rng,1),
        upvt_head,TAKE(upvt_rng,1),
        pvt_col_count,COLUMNS(pvt_data),
        upvt_col_count,COLUMNS(upvt_data),
        total_rows,ROWS(pvt_data)*upvt_col_count,
        pvt_idx,RPTCOL(total_rows,pvt_col_count),
        upvt_idx,RPTROW(total_rows,pvt_col_count,upvt_col_count),
        pvt_res,INDEX(pvt_data,upvt_idx,pvt_idx),
        atr,ATT(total_rows,upvt_head),
        val,PVAL(upvt_data,total_rows,upvt_col_count),
        HSTACK(
            HSTACK(
                VSTACK(pvt_head,pvt_res),
                VSTACK("Attribute",atr)
            ),
            VSTACK("Value",val)
        )
    )
)

UNPIVOT
	A	B	C	D	E	F	G	H	I	J
1	Company	Area	Color	ID	Letter		Company	Area	Attribute	Value
2	CompanyA	North	Red	1	A		CompanyA	North	Color	Red
3	CompanyB	South	Blue	2	B		CompanyA	North	ID	1
4	CompanyC	East	Yellow	3	C		CompanyA	North	Letter	A
5	CompanyD	West	Green	4	D		CompanyB	South	Color	Blue
6							CompanyB	South	ID	2
7							CompanyB	South	Letter	B
8							CompanyC	East	Color	Yellow
9							CompanyC	East	ID	3
10							CompanyC	East	Letter	C
11							CompanyD	West	Color	Green
12							CompanyD	West	ID	4
13							CompanyD	West	Letter	D
Sheet3

Cell Formulas
Range	Formula
G1:J13	G1	=UNPIVOT(A1:B5,C1:E5)
Dynamic array formulas.

Additional helper lambdas...

RPTROW

=LAMBDA(
    ro,co,div,
    MAKEARRAY(
        ro,co,
            LAMBDA(
                r,c,
                    INT((r-1)/div)+1
            )
    )
)

RPTCOLUMN

=LAMBDA(
    ro,co,
        MAKEARRAY(
            ro,co,
                LAMBDA(
                    r,c,c
                )
            )
)

ATT

=LAMBDA(
    ro,h,
        LET(
            co,COLUMNS(h),
            s,SEQUENCE(ro,1,0),
            INDEX(h,MOD(s,co)+1
        )
    )
)

PVAL

=LAMBDA(
    rng,ro,co,
        LET(
            s,SEQUENCE(ro,,0),
            rx,INT(s/co)+1,
            cx,MOD(s,co)+1,
            INDEX(rng,rx,cx)
        )
)

 

[jaeiow]

Would you know how to apply the cosmetic grouping. In other words, every second instance of the same Index would appear as blank. I had a little hack where I expanded it with blanks and then sorted it. But I don't see how I could implement that here.

 

[jaeiow]

Would you know how to apply the cosmetic grouping. In other words, every second instance of the same Index would appear as blank. I had a little hack where I expanded it with blanks and then sorted it. But I don't see how I could implement that here.

For this quick fix (if you can call it that), calls "AREPT", but this is a first column only solution (my requirement is met). Above posted the multicolumn function. Thanks for that by the way, @lrobbo314

Unpivot = LAMBDA(pivot_cols, [cosmetic],
    LET(
        pvt_rng, CHOOSECOLS(pivot_cols, 1),
        upvt_rng, DROP(pivot_cols, , 1),
        pvt_data, DROP(pvt_rng, 1),
        upvt_data, DROP(upvt_rng, 1),
        upvt_head, TAKE(upvt_rng, 1),
        pvt_col_count, COLUMNS(pvt_data),
        upvt_col_count, COLUMNS(upvt_data),
        pvt_rows, ROWS(pvt_data),
        total_rows, pvt_rows * upvt_col_count,
        stacked_s, AREPT(SEQUENCE(upvt_col_count), pvt_rows),
        pvt_idx, RPTCOL(total_rows, pvt_col_count),
        upvt_idx, RPTROW(total_rows, pvt_col_count, upvt_col_count),
        pvt_res, IFERROR(
            INDEX(
                pvt_data,
                IF(
                    OR(ISOMITTED(cosmetic), NOT(cosmetic)),
                    upvt_idx,
                    IF(stacked_s = 1, upvt_idx, 999)
                ),
                pvt_idx
            ),
            IF(stacked_s = 2, "Remark", "")
        ),
        atr, ATT(total_rows, upvt_head),
        val, pval(upvt_data, total_rows, upvt_col_count),
        HSTACK(HSTACK(pvt_res, atr), val)
    )
);