• Marek Vavrovic

Power Query: Normalizing denormalized table

Sample data:

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

  1. 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




37 views0 comments