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

Question

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

Dataset has the following data :-

Date	Year	Month	Day	USD	EUR	JPY
1/1/1994	1994	1	1	10	20	5
1/1/1995	1995	1	1	12	30	10

The expected output is :-

Date	Year	Month	Day	Currency	Currency/CCY
1/1/1994	1994	1	1	USD	10
1/1/1994	1994	1	1	EUR	20
1/1/1994	1994	1	1	JPY	5
1/1/1995	1995	1	1	USD	12
1/1/1995	1995	1	1	EUR	30
1/1/1995	1995	1	1	JPY	10

Answer 1

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))

---