Search code examples
excelexcel-formulaoffice365ms-officems-office-script

Format Excel data in a particular case

My data in Excel is

enter image description here

I want this data using the formula

enter image description here

Solution

  • Using MS365 Formulas and Power Query

    enter image description here

    • 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.