Format Excel data in a particular case

Question

My data in Excel is

I want this data using the formula

Answer 1

Using MS365 Formulas and Power Query

---

• Formula used in cell I1

=LET(_rlabels,A2:A5,_years,B1:G1,_yeardata,B2:G5,
VSTACK({"Row Labels","Years","Value"},
HSTACK(TOCOL(IFNA(_rlabels,SEQUENCE(,COLUMNS(_yeardata)))),
TOCOL(IFNA(_years,SEQUENCE(ROWS(_yeardata)))),
TOCOL(_yeardata))))

---

Or,

• Formula used in cell M1

=LAMBDA(array,
VSTACK({"Row Labels","Years","Value"},
HSTACK(TEXTSPLIT(TEXTJOIN("|",1,
DROP(TAKE(array,,1)&"-"&TAKE(array,1),1,1)),"-","|"),
TOCOL(DROP(array,1,1)))))(A1:G5)

---

Or, Using Power Query

• Select some cell in your Data Table,

• Data Tab => Get&Transform => From Table/Range,

• When the PQ Editor opens: Home => Advanced Editor,

• Make note of all the 2 Tables Names,

• Paste the M Code below in place of what you see.

---

let
    Source = Excel.CurrentWorkbook(){[Name="DataTbl"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Row Labels"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Years"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", type number}})
in
    #"Changed Type"

---

• Change the Table name from DataTbl to as Table_Outputbl before importing it back into Excel, note that you need to select from Home tab --> Close & Load --> Close & Load To --> Select either Existing Worksheet or New Worksheet as per your preferences.

---