Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
  • edhans's avatar
    edhans
    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 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"