Forum Discussion
New Table
- 6 years ago
Yes, in Power Query, just select the date column, right-click and select Unpivot Other Columns, rename them then get rid of the attribute column. Returns this:
To see this work, use the M code below:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Donelet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLRN9M3MjAyADJRUKxOtJIRWN4QJm+ib4RgGiOJWiKbANJojK7RFEm1BZp9IA0m6MabIWlA1myObpUpulXIOpEMNDLGwTaBsGNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ead#" = _t, #"Date of 1st Call " = _t, #"Date of 2nd Call " = _t, #"Date of 3rd Call" = _t, #"Date of 4th Call" = _t, #"Date of 5th Call" = _t, #"Date of 6th Call" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ead#", Int64.Type}, {"Date of 1st Call ", type date}, {"Date of 2nd Call ", type date}, {"Date of 3rd Call", type date}, {"Date of 4th Call", type date}, {"Date of 5th Call", type date}, {"Date of 6th Call", type date}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ead#"}, "Attribute", "Call Date"), #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"ead#", "Call Date"}) in #"Removed Other Columns"
Yes, in Power Query, just select the date column, right-click and select Unpivot Other Columns, rename them then get rid of the attribute column. Returns this:
To see this work, use the M code below:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLRN9M3MjAyADJRUKxOtJIRWN4QJm+ib4RgGiOJWiKbANJojK7RFEm1BZp9IA0m6MabIWlA1myObpUpulXIOpEMNDLGwTaBsGNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ead#" = _t, #"Date of 1st Call " = _t, #"Date of 2nd Call " = _t, #"Date of 3rd Call" = _t, #"Date of 4th Call" = _t, #"Date of 5th Call" = _t, #"Date of 6th Call" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ead#", Int64.Type}, {"Date of 1st Call ", type date}, {"Date of 2nd Call ", type date}, {"Date of 3rd Call", type date}, {"Date of 4th Call", type date}, {"Date of 5th Call", type date}, {"Date of 6th Call", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ead#"}, "Attribute", "Call Date"),
#"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"ead#", "Call Date"})
in
#"Removed Other Columns"- Anonymous6 years agoNot applicable
edhans I figured out what I was doing wrong.
One last question - how the unpivot table can be saved a a separate table?
- Anonymous6 years agoNot applicable
Hi Anonymous ,
There's no "unpivot table as new" feature like "merge queries as new" currently.
You can right click the raw table then copy and paste as new table then do the unpivot operation.
Best Regards,
Jay
- edhans6 years ago
Community Champion
Anonymous wrote:
edhans I figured out what I was doing wrong.
One last question - how the unpivot table can be saved a a separate table?
Before doing the unpivot, right-click on the query you want to unpivot, then select "Reference" then do the unpivot on the linked table. Now all changes to the original will remain, and the unpivoted table will use that data and unpivot all new and changed information.