Unpivot data

[Lara_1234]

Hi experts, i have below data which i need to unpivot by year

Country	2013 Account	2013Unit	2013rate	2013cost	2014Group Account	2014Unit	2014rate	2014cost	2015Group Account	2015Unit	2015rate	2015cost
UK	ABC	10	12	15	HIJ	19	21	24	QRS	38	40	43
UK	BCD	11	13	16	KLM	20	22	25	TUV	39	41	44
UK	EFG	12	14	17	NOP	21	23	26	WXY	40	42	45

 

[pecoflyer]

Please post a sample sheet. No pics please. Thx

 

[Lara_1234]

Have attached sample sheet.

 

[p45cal]

Power Query result at cell R3, pivot from that table at cell X4 in the attached.

 

[Peter Bartholomew]

This addresses the problem using Excel 365 formulas and is not for the faint-hearted!

The first step was to create a Lambda function that would extract the data for a single year as a function (a thunk).

/*  FUNCTION NAME:  ExtractYrDataλ
    DESCRIPTION:    Looks up and extracts 1 year's data from table as a thunk*/
/*  REVISIONS:      Date            Developer           Description
                    27 Apr 2024     Peter Bartholomew   Original Development
*/

ExtractYrDataλ = LAMBDA(
//Parameter Declarations
    hdr,            //Header row to crosstab table
    body,           //Body of crosstab table
    LAMBDA(
    yr,             //Year of data to be extracted
    LAMBDA(
        LET(
            firstField, XLOOKUP(yr&"*", hdr, body,,2, 1),
            finalField, XLOOKUP(yr&"*", hdr, body,,2,-1),
            IFERROR(HSTACK(yr, firstField:finalField), "")
        )
    )
))

The function looks up the first and final fields corresponding to the given year and composes the range using the ":" operator. It appends the year and thunks the result. The result is shown as the first formula on the worksheet.


That formula is

= ExtractYrDataλ(Table1[#Headers], Table1)(2014)()

The second formula shows how the thunk array is built

= MAP({2013; 2014; 2015},
    ExtractYrDataλ(Table1[#Headers], Table1)
  )

The final formula brings this together

=LET(
    yrTblϑ, MAP({2013; 2014; 2015}, ExtractYrDataλ(Table1[#Headers], Table1)),
    output, REDUCE(
        {"Year", "Account", "Unit", "Rate", "Cost"},
        yrTblϑ,
        LAMBDA(stack, ϑ, VSTACK(stack, ϑ()))
    ),
    output
)

REDUCE is used to feed the elements of the thunk array into the lambda function one by one where they are evaluated and stacked.

If this looks like pretty heavy programming than spreadsheet authoring, that is not far from the truth! The amazing thing is that it works!

 

[djc]

Assuming the number of fields per year is consistent (in the same order with the same field names), another dynamic array option for MS365 is to perform a basic unpivot with TOCOL and repivot with WRAPROWS, then SORT.

=LET(
    table,  C3:O6,
    header, DROP(TAKE(table, 1),, 1),
    fields, PROPER(UNIQUE(TRIM(REPLACE(header, 1, 4, )), 1)),
    labels, LAMBDA(n, TOCOL(CHOOSE(n, TAKE(DROP(table, 1),, 1), --UNIQUE(LEFT(header, 4), 1)))),
    output, HSTACK(labels({1}), labels({2}), WRAPROWS(TOCOL(DROP(table, 1, 1)), COLUMNS(fields))),
    VSTACK(HSTACK("Country", "Year", fields), SORT(output, {1,2}, {1,-1}))
)

Adjust the table range as needed, and the sort_order as desired. You could also insert blank lines after each item label and remove the repeated labels with a few extra steps...

=LET(
    table,  C3:O6,
    header, DROP(TAKE(table, 1),, 1),
    fields, PROPER(UNIQUE(TRIM(REPLACE(header, 1, 4, )), 1)),
    labels, LAMBDA(n, TOCOL(CHOOSE(n, TAKE(DROP(table, 1),, 1), --UNIQUE(LEFT(header, 4), 1)))),
    output, HSTACK(labels({1}), labels({2}), WRAPROWS(TOCOL(DROP(table, 1, 1)), COLUMNS(fields))),
    group,  SORT(VSTACK(output, EXPAND(UNIQUE(TAKE(output,, 2)),, COLUMNS(output), "")), {1,2}, {1,-1}),
    items,  TAKE(group,, 2),
    noRept, HSTACK(IF(VSTACK({"",""}, DROP(items, -1))=items, "", items), DROP(group,, 2)),
    VSTACK(HSTACK("Country", "Year", fields), noRept)
)

Cheers!