How to transpose data from an Excel sheet

[Jaweed]

Hi

Can anyone be of help to me?  What are the steps to do do so? Thanks

My Excel sheet is of the format below with lots of rows

Name     Jan      Feb     March     

John        100     200    300         

Lisa          500     600    700         

I would like to transform the data  like below

Name  Date  Amount

John   Jan        100

John   Feb       200 

John   March   300

Lisa   Jan          500

Lisa   Feb         600 

Lisa   March     700

[lbendlin · Accepted solution]

Use the Unpivot transform.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyDu0QElHydDAAEwbQWljMB2rE63kk1mcCBYyhUqZQWlziJJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Name " = _t, #"Jan " = _t, #"Feb " = _t, #"March " = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name "}, "Date", "Amount")
in
    #"Unpivoted Other Columns"

How to use this code: Create a new Blank Query, then click on "Advanced Editor", and then replace the code in the window with the code provided here. Then click "Done".