Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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.

View solution in original post

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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.
Anonymous
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.