New Table

[Anonymous]

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

CallDate2  4/5/20

CallDate3  5/1/20

I want to create a  second table to be able to measure the calldates by lead#

so the second table structure needs to be:

lead#1  

CallDate1 3/4/20

Lead #1

CallDate2  3/5/20

Lead #1 

CallDate3 4/1/20

Lead #2 

CallDate1  4/1/20

Lead # 2 

CallDate2  4/5/20

Lead # 2

CallDate3  5/1/20

That way I can use a matrix to keep a count of calls by lead#.  How can I accomplish this in Power BI? 

[Anonymous]

Lead#	Date of 1st Call	Date of 2nd Call	Date of 3rd Call	Date of 4th Call	Date of 5th Call	Date of 6th Call
1	4/6/2020
2	4/1/2020	4/2/2020	4/3/2020	4/29/2020
3	4/1/2020	4/5/2020	4/28/2020
4	4/2/2020	4/6/2020	4/25/2020	4/27/2020
5	4/1/2020	4/6/2020	4/22/2020	4/23/2020	4/23/2020	4/24/2020

the above data is the flat file.  I want to be able to count each date as a call.  I believe the structure of the new table should be:

Lead#
1	4/6/2020
2	4/1/2020
2	4/2/2020
2	4/3/2020
2	4/29/2020
3	4/1/2020
3	4/5/2020
3	4/28/2020
4	4/2/2020
4	4/6/2020
4	4/25/2020
4	4/27/2020
5	4/1/2020
5	4/6/2020
5	4/22/2020
5	4/23/2020
5	4/24/2020

[edhans · Accepted solution]

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"

[Anonymous]

@edhans I figured out what I was doing wrong. 

One last question - how the unpivot table can be saved a a separate table? 

[edhans]

---

@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.

[Anonymous]

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

[Anonymous]

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?

[Anonymous]

[edhans]

I don't understand your tables? Those are lists really, not tables. Tables have fields. WHere is "Lead" in all of this? Is that a field? If you could share your data in a grid format using the directions below it would be easier to attack this problem.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum