Search code examples
excelexcel-formulapowerquerytransposedata-transform

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

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
Solution

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

    enter image description here

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

    enter image description here

    Or, Using Excel Formulas to UNPIVOT OTHER COLUMNS

    enter image description here

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