Need Help Transforming Pivoted Data

[Anonymous]

I have a list of customer contacts via an Excel export that I must first perform a data transformation before connecting to it in PBI. 

My data contains the customer name and then 3 sets of fields for contact info (i.e. First Name, Last Name, Title, Phone, Email). Each row is a different customer. The issue I'm having is that I would like to take the "pivoted" contact info that is spread across numerous columns and "unpivot" it so that I would have exactly 3 rows for each customer and each row would contain the contact info I listed above. 

I've tried to "unpivot" the data which ends up giving me 15 rows for each customer as each column name has to be unique (i.e. First Name 1, First Name 2, First Name 3, Last Name 1, Last Name 2, Last Name 3, etc). I then split the column to remove the number on the end and tried to re-pivot the data in hopes that I could get the data to look like the image below. Since I'm telling Power Query "Don't Aggregate," I get an error in my results that says "There were too many elements in the enumeration to complete the operation." Does anyone know how I can get around this or a better way to accomplish this transformation in Power Query?

  

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

For your requirement, I'm afraid that we should use Append Queries in Query Editor.

Here is my test data sample which is similar to yours.

CustomerFirst Name 1Last Name 1Title 1Phone 1Email 1First Name 2Last Name 2Title 2Phone 2Email 2

Customer A	A1	1A	A	1231456	1231456@	A2	2A	AA	456789	456789@
Customer B	B1	1B	B	789456	789456@	B2	2B	BB	741852	741852@

You could duplicate the table and then remove the columns Name 1Last Name 1Title 1Phone 1Email 1 in Table 2 and remove Name 2Last Name 2Title 2Phone 2Email 2 in Table 2 and rename the column name.

Then Append table 1 and table 2.

More detail steps, please refer to my attachment.

Best  Regards,

Cherry

[Anonymous]

Cherry,

This is exactly what I was looking for! I got tunnel vision on the "pivot/unpivot" columns that I missed such a simple and easy solution. Thanks so much for your help!

All the best,

Chris