Handling Merged Headers in Excel with Power Query

Hello Friends!! In this blog post, we will explore a Power Query M script that performs a series of transformations on a table from an Excel workbook. This script refines and reshapes the data, making it more structured and analysis-friendly. Let’s break it down step by step and file attached to download.

Data

Here is the data source as you can see the column6 to column10 are merged and column11 to column14 merged.

Transforming Data in Power Query: A Step-by-Step Breakdown

Understanding the Source Data

The script begins by referencing an Excel table named Table1 and loading its content:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

This brings the table’s data into Power Query for further transformation.

Extracting and Merging Header Information

#"Kept First Rows" = Table.Transpose(Table.FirstN(Source,2)),

#"Filled Down" = Table.FillDown(#"Kept First Rows",{"Column1"}),

#"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),

• The first two rows of the table are transposed so that column headers become rows.
• The values in the first column are filled down to ensure continuity.
• The first two columns are then merged using a hyphen (-) as a delimiter.

Renaming Columns Dynamically

Custom1 = List.Zip({Table.ColumnNames(Source), #"Merged Columns"[Merged]}),

Custom2 = Table.RenameColumns(Source,Custom1),

• A dynamic renaming strategy is used: List.Zip pairs existing column names with the newly merged column names.
• The columns in the original table are renamed accordingly.

Cleaning Up the Data

#"Removed Top Rows" = Table.Skip(Custom2,2),

• The first two rows (which contained header-related information) are removed.

Standardizing Column Names

#"Renamed Columns1" = Table.RenameColumns(#"Removed Top Rows",{{"-Scope", "Scope"}, {"-Risk Category", "Risk Category"}, {"-Metric Name", "Metric Name"}, {"-Metric Id", "Metric Id"}, {"-Gover Committee", "Gover Committee"}}),

• Specific columns are renamed for better readability.

Unpivoting Data for Better Analysis

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"Scope", "Risk Category", "Metric Name", "Metric Id", "Gover Committee"}, "Attribute", "Value"),

• All columns except the specified ones (Scope, Risk Category, Metric Name, Metric Id, and Gover Committee) are unpivoted, turning column names into attribute-value pairs.

Splitting Attribute Column

#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),

• The Attribute column is split into two parts based on the hyphen (-).

Transforming and Renaming Columns

#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),

#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute.2", "Header"}}),

• The newly created columns are assigned proper data types.
• The second split column (Attribute.2) is renamed to Header.

Pivoting Data Back for Final Structure

#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Header]), "Header", "Value", List.Sum),

• The Header column values are used as new column headers, and their corresponding values are aggregated using List.Sum.

Sorting the Data

#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Attribute.1", Order.Ascending}})

• The final table is sorted based on Attribute.1 in ascending order.

Final Data

Conclusion

This Power Query M script successfully transforms raw Excel table data into a structured format, making it easier to analyze. By dynamically renaming columns, unpivoting and pivoting data, and organizing the dataset efficiently, this script showcases the power of Power Query for data transformation.

If you’re working with messy data and need to automate transformation steps, Power Query provides a robust and repeatable solution!

Download the file here

Keep visiting Analytics Tuts for more tutorials.

Thanks for reading! Comment your suggestions and queries.