Transform Excel table columns not all into rows - long to wide transformation

0 votes

Need to convert the columns in an Excel table to rows. I attempted to replace the value fields in a pivot table with its underlying data by text-converting the pivot table's values, but I was unable (to show them as text). I'm open to trying an alternate strategy for changing the relevant table.

I tried utilizing Power Query, unpivoting columns, and transposing, but I was unable to achieve the desired outcome.

Due to the fact that the current table contains more than 2,000 rows with legal names but only three separate groups for such rows, placing the group names side by side makes them easier to read and remember. The table's structure is similar to the records below:

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

This image portrays what it is shown in the above tables but with additional records to show realistic examples

Jan 8 in Others by Kithuzzz
• 27,740 points
35 views

1 answer to this question.

0 votes

enter image description here

In PQ, highlight the Group column and select pivot from the ribbon.

Fill in as follows and you're done:

enter image description here

answered Jan 8 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
0 answers
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 51,240 points
100 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 51,240 points
1,309 views
0 votes
1 answer

Add some word to all or some rows in Excel?

Solution: Select All cells that want to change. Right ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 51,240 points
60 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,640 points
165 views
0 votes
1 answer

Transpose column on one sheet to row on another, with linking

select A1:J1 in worksheet B and enter: =TRANSPOSE('worksheet ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 51,240 points
124 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
205 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
364 views
0 votes
1 answer

Transposing columns to rows in Excel

Due to the amount of your data, ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 51,240 points
162 views
0 votes
1 answer

Excel: How to merge two columns into one (from different sheets or separated columns)

This equation is completely adjustable. Your two ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 51,240 points
133 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP