For semi-structured Excel files that have to be transformed into a relational format for use in Power BI, Power Query provides an array of tools that help to clean and reshape inconsistent layouts into normalized tables. When header names are located over a number of rows or when data starts at different positions, dynamic logic has to come into play to ensure that the transformation can be repeated and refreshed.
Beginning with dynamic functions like Table. Skip or Table.PromoteHeaders, set about removing top rows and promoting the appropriate header row. Inspect the content and identify starting points for the real header (for example, run specific keywords through rows using Table.FindText or through custom filters). If there are multiple header rows, transpose them using Table. Transpose, merge them and then transpose them back so as to end up with single-line headers.
After cleaning up the headers, execute Unpivot Columns to normalize cross-tabbed or matrix-style data and Fill Down to deal with hierarchical or structurally missing information in key columns. This step-by-step cleanup turns the semi-structured Excel sheets into a flat and relational table for effective modeling inside Power BI-Ready for visuals, relationships, and DAX measures.
