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

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? 

 

  • 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"

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    Lead#Date of 1st Call Date of 2nd Call Date of 3rd CallDate of 4th CallDate of 5th CallDate of 6th Call
    14/6/2020     
    24/1/20204/2/20204/3/20204/29/2020  
    34/1/20204/5/20204/28/2020   
    44/2/20204/6/20204/25/20204/27/2020  
    54/1/20204/6/20204/22/20204/23/20204/23/20204/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# 
    14/6/2020
    24/1/2020
    24/2/2020
    24/3/2020
    24/29/2020
    34/1/2020
    34/5/2020
    34/28/2020
    44/2/2020
    44/6/2020
    44/25/2020
    44/27/2020
    54/1/2020
    54/6/2020
    54/22/2020
    54/23/2020
    54/24/2020

     

     

    • edhans's avatar
      edhans
      Community Champion

      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's avatar
        Anonymous
        Not applicable

        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?