cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chris1 Frequent Visitor
Frequent Visitor

Need Help Transforming Pivoted Data

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?

 

  Capture.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Need Help Transforming Pivoted Data

Hi @Chris1 ,

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.

Append queries.PNG

More detail steps, please refer to my attachment.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Highlighted
Community Support Team
Community Support Team

Re: Need Help Transforming Pivoted Data

Hi @Chris1 ,

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.

Append queries.PNG

More detail steps, please refer to my attachment.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Chris1 Frequent Visitor
Frequent Visitor

Re: Need Help Transforming Pivoted Data

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