Sample data:
I have this Denormalized table, and I want to put the table into 3rd normal form.
Load table into Power Query
2. Duplicate the original table 3 times
3. Now we have the original table and 3 copy of it.
4.
The original table has LastName column and 3 sets of FirstName and Age.
Copy1 table
Create a first Table that will contain LastName, FirstName, Age columns, and remove the other columns.
Copy2 table
Highlight LastName, FirstName, Age, and remove other columns.
Copy3 table
Highlight LastName, FirstName, Age, and remove other columns.
Appending tables
Now each of these copy tables contains LastName, FirstName and Age and we can Append them as New.
Choose Copy1 ,2 and 3 for Append.
The result is a table with 1 LastName column and we also have 3 columns for Age and FirstName with NULL values.
Next step is to highlight all 3 Age columns and apply Merge Columns transformation. That will help us to get rid of the null values.
Separator None, choose a column name for the merged column.
You can see the merged column Age.1 on the right side. Now apply the same transformation for all 3 FirstName columns. Highlight them > Merge Columns.
At this stage we manage to remove the nulls.
We can remove empty now.
This is done. Do not load into Excel yet, just create the connections.
In Excel, click on the connection from the Appended file. Choose Load To...
Specify where you want to load that table.
We have loaded just the normalized table, the rest of them are connections and won't be loaded into excel.
Another way how to do it without duplicating the original table
Unpivot only selected columns.
Add a Custom column
Add a Custom Column for FirstName
Add a Custom Column for Age
Fill Down for FirstName column, Fill Up for Age Column
Group by LastName, FirstName, Age
Operation: All Rows
Remove the last column: Count
Result
