Promote headers from a specific row

This solution takes a slightly different approach.

1. Filter for your header row. This will remove all other records in the query except your header row you want. You do not need to know the row number it is on.
2. Hit the Append Queries button on the Home tab, and append the table to itself.
3. You now have your header row shown twice.
4. In the formula bar, replace the second table in the Append operation (the Table.Combine function) with the name of the step directly above the filtered for header row step.
5. Now promote the header row.
6. Filter the header row out, as it will be duplicated from the Table.Combine operation.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Filtered for header row" = Table.SelectRows(#"Changed Type", each ([Data] = "Header Row")),
    #"Appended Query" = Table.Combine({#"Filtered for header row", #"Changed Type"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Appended Query", [PromoteAllScalars=true]),
    #"Filtered out header row" = Table.SelectRows(#"Promoted Headers", each ([Header Row] <> "Header Row"))
in
    #"Filtered out header row"

So the steps will look like those below. Note I've manually renamed a few. The "Appended Query" step is appending the table created by the "Changed Type" step below the single record table created by the "Filtered for header row" step.