How to transpose columns into rows and ensure repetition of rows accordingly in excel?

How to transpose the columns and ensure that rows are repeated accordingly?

Dataset has the following data :-

The expected output is :-

This can be accomplished quickly and easily using POWER QUERY. To achieve this using the said procedure, follow the steps:

---

• First convert the source ranges into a table and name it accordingly, for this example I have named it as Table1

---

• Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

---

• The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

---

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date", "Year", "Month", "Day"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Date", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute", "Currency"}, {"Value", "Currency/CCY"}})
in
    #"Renamed Columns"

---

---

• Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

---

---

Or, Using Excel Formulas to UNPIVOT OTHER COLUMNS

---

• Formula used in cell A6

=LET(
    _data, A1:G3,
    _matrixOne, TAKE(_data,,4),
    _matrixTwo, DROP(_data,,4),
    _headerOne, TAKE(_matrixOne,1),
    _headerTwo, TAKE(_matrixTwo,1),
    _bodyOne, DROP(_matrixOne,1),
    _bodyTwo, DROP(_matrixTwo,1),
    _diemn, ROWS(_bodyOne)*COLUMNS(_headerTwo),
    _rowDiemn, MOD(SEQUENCE(_diemn,,0),COLUMNS(_headerTwo))+1,
    _rowDiemx, INT((SEQUENCE(_diemn,,0))/COLUMNS(_headerTwo))+1,
    _transformHeader, INDEX(_headerTwo,_rowDiemn),
    _transformMatrixOne, INDEX(_bodyOne,_rowDiemx,SEQUENCE(1,COLUMNS(_matrixOne))),
    _transformMatrixTwo, INDEX(_bodyTwo,SEQUENCE(_diemn,,0)/COLUMNS(_headerTwo)+1,_rowDiemn),
    _topHeader, HSTACK(_headerOne,"Currency","Currency/CCY"),
    _bottomBody, HSTACK(_transformMatrixOne,_transformHeader,_transformMatrixTwo),
    VSTACK(_topHeader,_bottomBody))

---