Need to transform an Excel table's column into rows. I tried converting a pivot table's values into text but could not find a way to replace the value fields with its underlying data (to show as text). I am open to a different approach on transforming the table in question.
I tried using using Power Query and unpivoting columns and also transposing but could not get the desired result.
Current table includes 2,000 rows with legal names, but only three different groups for such rows, so having the group names side by side is more legible and easier to reference. The structure of the table resembles the below records:
| Group | Legal Name | DBA |
|---|---|---|
| A | 2023 LTD, Inc | 2023 LTD Inc |
| B | 2023 LTD, Inc | 2023 LTD |
| C | 2023 LTD, Inc | 2023 LTD Inc. |
| A | XYZ Enterprises | XYZ Enterprises Inc. |
| A | Aerotec, LLC | Aerotec |
| B | Aerotec, LLC | Aerotec LLC. |
| C | Aerotec, LLC | Aero |
Desired Result:
| Legal Name | A | B | C |
|---|---|---|---|
| 2023 LTD, Inc | 2023 LTD Inc | 2023 LTD | 2023 LTD Inc. |
| XYZ Enterprises | XYZ Enterprises Inc. | ||
| Aerotec, LLC | Aerotec | Aerotec LLC. | Aero |
In PQ, highlight the Group column and select pivot from the ribbon.
Fill in as follows and you're done:
