Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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".
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".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |