This Power Query custom function will right-fill nested headers then combine n levels of headers into a single promoted column header

powerquery-m-fixnestedheaders.txt

/*
Author: Owen Price
Date: 2022-09-17

https://www.flexyourdata.com/

Handles multi-level nested column headers, optionally combines them into a single header row and optionally promotes the first row to the column header of the resulting table.
*/
let 
    func = 
        (
            data as table,
            columns as list,
            levels as number,
            optional combine as logical,
            optional separator as text,
            optional promote as logical
        ) as table =>

        let

            //Simple function to handle optional parameters
            ifnull = (param as any, thenval as any) as any => if param is null then thenval else param,

            //Handle null/missing combine parameter
            _combine = ifnull(combine,true),

            //Handle null/missing promote parameter
            _promote = ifnull(promote,true),

            //Handle null/missing separator parameter
            _separator = ifnull(separator,"-"),

            //Define a function to check whether or not the current row is in the list of columns passed to the function or not
            get_columns_to_fill = (bool as logical) as function => (row as record) as logical => List.Contains(columns, row[Column1]) = bool,

            //Split the table into the headers and the non-headers
            split_table = Table.SplitAt( Table.DemoteHeaders( data ) , levels + 1 ),

            //Transpose the table containing the header levels
            transposed_headers = Table.Transpose( split_table{0} ),

            /*
            Here we select only those transposed rows where the value in Column1 (i.e. the original column header) is in the columns parameter.

            get_columns_to_fill(true) creates the following function:
                (row as record) as logical => List.Contains(columns, row[Column1]) = true

            Since this is now a one-parameter function, we can use it in the second parameter of Table.SelectRows, which
            itself passes each record in the table into the "row" parameter of the function.
            */
            headers_to_fill = Table.SelectRows( transposed_headers , get_columns_to_fill(true) ),

            /*Once we have only those rows we want to work on, we use FillDown on all of them*/
            filled_headers = Table.FillDown( headers_to_fill, Table.ColumnNames( headers_to_fill ) ),

            /*
            Here we use Table.Selectrows with the function created by get_columns_to_fill(false):
                (row as record) as logical => List.Contains(columns, row[Column1]) = false

            This selects those rows from the transposed data whose value in Column1 (i.e. the original column header)
            is NOT in the columns parameter and appends them to the filled-down data (the data modified above)
            */
            fixed_headers = Table.Combine( { Table.SelectRows( transposed_headers , get_columns_to_fill(false) ) , filled_headers } ),

            //If the user has requested, combine the header levels into a single column
            conditional_combine 
                    = if _combine then 
                        let
                            //create a list of column names in the transposed table which we will merge into a single column
                            columns_to_merge = List.Transform({ 2..levels + 1 }, each "Column" & Number.ToText(_)),

                            //perform the column merge into a new column called "New header"
                            merged_column = Table.CombineColumns( fixed_headers , columns_to_merge , Combiner.CombineTextByDelimiter(_separator) , "New header" ),

                            //drop Column1 since we don't want it any more
                            combined = Table.RemoveColumns( merged_column , {"Column1"} )
                        in combined
                    else fixed_headers,

            //Finally, transpose the header back to a horizontal orientation and re-join with the data rows
            final_table = Table.Combine( { Table.Transpose(conditional_combine) , split_table{1} } ),

            //If the user has requested, promote the first row to the column headers
            result = if _promote then Table.PromoteHeaders( final_table ) else final_table
        in result,
    documentation = [
        Documentation.Name = "FixNestedHeaders",
        Documentation.Description = "Applies a 'RightFill' operation to a multi-level nested header",
        Documentation.LongDescription = "<p>Applies a 'RightFill' operation to a multi-level nested header.</p>
        <p>Optionally combines the specified levels into a single row column header.</p>
        <p>Optionally promotes the first row in the resulting table to be the new column header.</p>
        <p>----------------------------------</p>
        <p>Owen Price - flexyourdata.com</p>",
        Documentation.Category = "Table",
        Documentation.Source = "https://www.flexyourdata.com",
        Documentation.Author = "Owen Price"
    ],
    my_type = 
    type function (
            data as (type table meta [Documentation.FieldCaption = "Table with nested headers"] ), 
            columns as (type list meta [Documentation.FieldCaption = "A list of column names in 'data'"]), 
            levels as (type number meta [Documentation.FieldCaption = "First N rows containing the nested headers", Documentation.SampleValues = { 2, 3, 4 }]), 
            optional combine as (type logical meta [Documentation.FieldCaption = "Combine the nested headers into a single row"]), 
            optional separator as (type text meta [Documentation.FieldCaption = "Separator when combining headers", Documentation.SampleValues = { "-" }]),  
            optional promote as (type logical meta [Documentation.FieldCaption = "Promote the first row to column headers"])
    ) as table
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(my_type, documentation))