# Unpivot data

#### Lara_1234

##### New Member
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

Please post a sample sheet. No pics please. Thx

Have attached sample sheet.

#### Attachments

• BR.xlsx
9.4 KB · Views: 5
Power Query result at cell R3, pivot from that table at cell X4 in the attached.

#### Attachments

• Chandoo56801BR.xlsx
24 KB · Views: 4
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).
Code:
``````/*  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

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

The second formula shows how the thunk array is built
Code:
``````= MAP({2013; 2014; 2015},
ExtractYrDataλ(Table1[#Headers], Table1)
)``````

The final formula brings this together
Code:
``````=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!

#### Attachments

• Chandoo56801BR.xlsx
33.3 KB · Views: 0
Last edited:
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.

Code:
``````=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...

Code:
``````=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!

#### Attachments

• BR_tocol_wraprows.xlsx
12 KB · Views: 1