Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
Solved! Go to 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
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |