Restructure the layout of datasets

I’d like to discuss 2 cases of restructuring layouts of datasets:

Case 1

Consider the 7 columns dataset below.  For every Doc Number, there are 6 columns – 3 columns for the name of the employee and another 3 columns for the Percent of each employee.

Doc Number	Emp 1	Emp 1 %	Emp 2	Emp 2 %	Emp 3	Emp 3 %
111111	John	5%	Eric	6%	Jane	7%
222222	Mary	10%	Joe	4%	Jill	25%

The objective is to restructure the data layout to a 3 column one  – Doc Number, Employee Name and Employee Percent.  Such a revised data layout would enable filtering, Pivoting, charting etc.  Furthermore, the solution should be dynamic for new columns added.  The expected data layout is:

Doc Number	Employee	Percent
111111	John	5%
111111	Eric	6%
111111	Jane	7%
222222	Mary	10%
222222	Joe	4%
222222	Jill	25%

I have solved this problem using Power Query a.k.a. Get & Transform under the Data menu in the Ribbon.  You may download my solution from here.

Case 2

Consider the 7 column dataset below (there are actually 13 columns.  For want of space only 7 have been shown).  For every Phone Number, there are up to 12 columns – 6 columns for the date on which the call was placed and the other 6 for the remark jotted down by the service agent based on the interaction with the customer.  The software from where the data is downloaded is capable of downloading only 6 columns per Phone Number.  If there were more than 6 calls placed on the same customer, then multiple rows will appear for that same number.  A case in point being Phone number 561 below – you will see that this number appears multiple times in the table below.

Phone	Date 1	Remark 1	Date 2	Remark 2	Date 3	Remark 3
561	12/03/17	Interested	17/03/17	Not Interested	22/03/17	Not Reachable
562	13-03-17	Not Interested	18-03-17	Call Busy	23-03-17	Not Reachable
561	14-03-17	Not Interested	19-03-17	Not Reachable	24-03-17	Call Dropped
564	15-03-17	Call Busy	20-03-17	Not Interested	25-03-17	Call Later
562	16-03-17	Call Busy	21-03-17	Call Busy	26-03-17	Call Busy
561	17-03-17	Interested	22-03-17	Call Later	27-03-17	Call Dropped
563	18-03-17	Call Later	23-03-17	Interested	28-03-17	Call Busy
568	19-03-17	Call Dropped	24-03-17	Call Busy	29-03-17	Call Busy
561	20-03-17	Call Busy	25-03-17	Not Reachable	30-03-17	Not Interested
565	21-03-17	Call Dropped	26-03-17	Call Later	31-03-17	Call Later
562	22-03-17	Interested
574	23-03-17	Not Interested	28-03-17	Call Dropped	02-04-17	Not Interested
563	24-03-17	Interested	29-03-17	Not Reachable	03-04-17	Call Later
572	25-03-17	Call Dropped	30-03-17	Call Busy	04-04-17	Interested
573	26-03-17	Call Dropped	31-03-17	Not Interested	05-04-17	Not Reachable

The objective here is to rearrange the layout so that only one row exists per Phone number.  The result should be dynamic for new Phone numbers added and the same number being repeated in many more rows.  The expected data layout is (for want of space only limited columns have been shown here):

Phone	Custom.1	Custom.2	Custom.3	Custom.4	Custom.5	Custom.6
561	3/12/2017	Interested	3/17/2017	Not Interested	3/22/2017	Not Reachable
562	3/13/2017	Not Interested	3/18/2017	Call Busy	3/23/2017	Not Reachable
564	3/15/2017	Call Busy	3/20/2017	Not Interested	3/25/2017	Call Later
563	3/18/2017	Call Later	3/23/2017	Interested	3/28/2017	Call Busy
568	3/19/2017	Call Dropped	3/24/2017	Call Busy	3/29/2017	Call Busy
565	3/21/2017	Call Dropped	3/26/2017	Call Later	3/31/2017	Call Later
574	3/23/2017	Not Interested	3/28/2017	Call Dropped	4/2/2017	Not Interested
572	3/25/2017	Call Dropped	3/30/2017	Call Busy	4/4/2017	Interested
573	3/26/2017	Call Dropped	3/31/2017	Not Interested	4/5/2017	Not Reachable

I have solved this problem using formulas and Power Query a.k.a. Get & Transform under the Data menu in the Ribbon.  You may download my solution from here.