How to use Power Query to Handle Multi‑Row Headers Dynamically from Excel to Power BI

How to use Power Query to Handle Composite Headers Dynamically from Excel to Power BI
Scenario:
Suppose we have a sample of FTE data in an Excel sheet where the actual headers begin at row number 4. However, the first three rows may contain additional text or values that provide context. For example, in a weekly allocation report, rows 1–3 might contain date ranges or descriptive labels above the actual header row, as shown below:

Considerations in Output:
When building the final table, we need to treat the first five columns differently from the last five:

• Columns 1 to 5 (A to E): These are structural fields such as UserID, UserName, Project_ID, FTE_Alloc_Status, and FTE_Percent. For these, we must ignore any text or values in rows 1 to 3 and simply use the header from row 4.
• Columns 6 to 10 (F to J): These represent weekly allocations (WK1 to WK5). For these, we must concatenate the text or values from rows 1 to 3 with the header in row 4 to form a composite header.
• Example: WK1_30-12-2024_03-01-2025.

This approach ensures that the weekly columns carry both the identifier (WK1, WK2, etc.) and the contextual date ranges, making the dataset more meaningful and self-explanatory.

We can achieve this scenario, using the below Power Query:

let

    /* LoadWorkbookAndSheet: 

       Load the Excel workbook from the given source path and access the target sheet "FTE_Sample". 

    */

    Source = Excel.Workbook(File.Contents(SrcPath), null, true),

    SrcData_Sheet = Source{[Item="FTE_Sample",Kind="Sheet"]}[Data],

    /* FindHeaderRowIndex: 

       Dynamically detect the row index where actual headers begin. 

       This is identified by locating "UserID" in the first column.

    */

    HeaderRowIndex = List.PositionOf(SrcData_Sheet[Column1], "UserID"),

    /* ExtractHeaderAndPreHeaderRows: 

       Capture all rows up to the header row. 

       Separate the HeaderRow (row containing actual column names) 

       and PreHeaderRows (rows above the header that may contain contextual values). 

    */

    AllRowsToHeader = List.FirstN(Table.ToRows(SrcData_Sheet), HeaderRowIndex+1),

    HeaderRow = AllRowsToHeader{HeaderRowIndex},

    PreHeaderRows = List.RemoveLastN(AllRowsToHeader, 1),

    /* BuildCompositeHeaders: 

       Construct raw composite headers by combining HeaderRow with PreHeaderRows. 

       Non-null values are concatenated using an underscore (_) to form meaningful column names. 

    */

    RawCompositeHeaders = List.Transform(

        List.Zip({HeaderRow} & PreHeaderRows),

        each Text.Combine(

            List.Select(List.Transform(_, Text.From), (x) => x <> null and x <> ""),

            "_"

        )

    ),

    /* ApplySkipLogic: 

       Define columns that should not use composite headers. 

       For these columns (e.g., "UserID", "FTE_Percent"), retain only the HeaderRow value. 

    */

    SkipColumns = {"FTE_Percent", "UserID"},

    CompositeHeaders = List.Transform(

        List.Zip({HeaderRow, RawCompositeHeaders}),

        each if List.Contains(SkipColumns, _{0}) then _{0} else _{1}

    ),

    /* PromoteHeadersAndRename: 

       Promote the detected HeaderRow as column names. 

       Then replace them with CompositeHeaders to ensure contextual information is included 

       for selected columns while keeping structural columns clean. 

    */

    PromoteHeaders = Table.PromoteHeaders(

        Table.Skip(SrcData_Sheet, HeaderRowIndex), 

        [PromoteAllScalars=true]

    ),

    FinalTable = Table.RenameColumns(

        PromoteHeaders, 

        List.Zip({Table.ColumnNames(PromoteHeaders), CompositeHeaders})

    )

in

    FinalTable

Output:

--------------------------------------------------------------------------------------------------------

Thanks

--------------------------------------------------------------------------------------------------------