Fix multi row headers in Power Query & native Excel
So many business spreadsheets are set up with dual-row headers or even 3, 4 or more rows for headers. Cells are merged across the top to make the table look nicer for data entry but this set up makes analysis much harder. Pivot Tables are blocked, filters appear in the wrong place and many more complications arise.
Excel's Analyze Data feature has a hidden aspect that can actually work to fix this with one click of a button but to make a more robust and flexible solution Power Query can assist.
Example files can be found here: www.xlconsulting-asia.com/youtube-files
Although there is the ability to merge columns in Power Query, merge rows isn't an available option so it is a multi-step solution, but I walk you through a no-code and a more elaborate low code solution that should get you through any situation. This solution could be combined with Unpivot to create a table with repeated column names
Contents
00:00 - Introduction
01:07 - Native Excel hack
02:43 - Power Query no code
07:28 - Recommended settings
08:29 - Low code with 4 header rows