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"
That worked great. But I have 2 questions:
1) If I want to use M code, the flat file was loaded and it became table name "AllLeads" (see visual) , you use table.fromrows. I can't just change the source and make it work right? I tried and it failed. The flat file has more columns than lead# and the dates. I didn't share that since it has names and accounts. Would that have to be part of the structure?
2) I also tried doing the first part, right-click and select unpivot. I am not getting the same results.
I select, lead#, and all the dates, then I select unpivot other columns. I get the columns but when I look at the data, I still get one record per lead#. I got rid of the attibute column. And I also tried selecting attribute lead#. What am I doing wrong?