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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!