Forum Discussion

Jaweed's avatar
Jaweed
Icon for Helper III rankHelper III
4 years ago
Solved

How to transpose data from an Excel sheet

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

 

 

Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

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

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

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