cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jaweed
Helper II
Helper II

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

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors