Promote headers from a specific row

[Anonymous]

Hi,

I have a need to promote headers from row 18. Is there any way to promote headers from a specific row in power query?

Since I also need to reference data from rows 1-17 after promoting the headers from row 18, solutions such as "delete first 17 rows" or "promote rows 18 times" is not a solution for this. 

If anyone knows a solution for this, i'd appreciate it!

[DavidHansen]

Very well explained. Thanks!

[edhans · Accepted solution]

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.

[Anonymous]

M can produce an efficient solution.

Skip the first 17 rows and promote headers like you normally would. These steps return the bottom of the query. Now go back to the source and keep the first 17 rows. Rename the top by zipping the column names of the top and the column names of the bottom. Finally, append the top and bottom together. Sample is below:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Top Rows" = Table.Skip(Source,17),
    Bottom = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    SourceTop = Table.FirstN(Source,17),
    Top = Table.RenameColumns(SourceTop,List.Zip({Table.ColumnNames(Source), Table.ColumnNames(Bottom)})),
    Custom1 = Top & Bottom
in
    Custom1

[AlB]

Hi @Anonymous 

You could do something like this

1. Add an index (or use another column to identify row 18, I assume you have one already)

2. Create a custom column that returns for instance "Promote" when the index is 18 and null otherwise.

3. Sort the custom column in descending order so that the row with "Promote" is on top

4. Promote first row to headers, as usual

5. Delete the two auxiliary columns we just created

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers