Forum Discussion
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?
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"
9 Replies
- edhansCommunity Champion
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 - AnonymousNot applicable
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 - edhansCommunity 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 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"- AnonymousNot 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?