Forum Discussion
Anonymous
6 years agoNot applicable
New Table
I would like to create a new table with the following structure. LeadsTable (structure) lead# 1 CallDate1 03/04/20 CallDate2 03/05/20 CallDate3 04/01/20 Lead#2 CallDate1 4/1/20 ...
- 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"
Anonymous
6 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?
Anonymous
6 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