Power Query: Normalizing denormalized table

Sample data:

I have this Denormalized table, and I want to put the table into 3rd normal form.

2. Duplicate the original table 3 times

3. Now we have the original table and 3 copy of it.

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