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, 2023 in Others by Kithuzzz
• 38,010 points
309 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, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
0 answers
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
• 63,720 points
658 views
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
• 63,720 points
604 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
• 63,720 points
7,368 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,670 points
818 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
• 63,720 points
1,156 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,670 points
2,429 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,670 points
1,674 views
0 votes
1 answer

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,720 points
554 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
• 63,720 points
923 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