Forum Discussion
Jaweed
Helper III
Helper IIIHow 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...
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".
lbendlin
Super User
Super UserUse 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".