FInd header row dynamically in Query

[jeongkim]

Hi,

my data has header in row 13 but not sure future change such to row 14 or 12. 

So any way making KPI ID row always header whereever they placed? 

If I use remove top rows then it cannot cover dynamic changes. 

[mh2587 · Accepted solution]

Try this one might help you
HeaderRowIndex = List.PositionOf(
Table.Column(Sheet, "Column1"),
"KPI ID"
)

[bhanu_gautam · Accepted solution]

@jeongkim 
Add a custom column to identify the row containing "KPI ID".
Filter the table to find the row number of the header.
Use that row number to set the headers dynamically.

Try using below M code

m
let
Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "IsHeader", each if Text.Contains([Column1], "KPI ID") then "Header" else null),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([IsHeader] = "Header")),
HeaderRowIndex = Table.FirstValue(#"Filtered Rows")[Index],
#"Removed Custom" = Table.RemoveColumns(#"Added Index",{"IsHeader", "Index"}),
#"Promoted Headers" = Table.PromoteHeaders(Table.Skip(#"Removed Custom", HeaderRowIndex), [PromoteAllScalars=true])
in
#"Promoted Headers"

[mh2587 · Accepted solution]

Try this one might help you
HeaderRowIndex = List.PositionOf(
Table.Column(Sheet, "Column1"),
"KPI ID"
)

[jeongkim]

I think it is working thanks

Custom1 = List.PositionOf(
Table.Column(#"Removed Columns", "Custom^Column1"),
"KPI ID"
),
#"Removed Top Rows" = Table.Skip(#"Removed Columns", Custom1),
#"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),

[jeongkim]

I've got this result which saying correct location of header row. 

what is the next pls? 

[Greg_Deckler]

@jeongkim If you are confident that at least one of the column headers will never change, you could identify the header row by looking for that column header name to dynamically identify the header row. If you post that sample data as text it will be easier for folks to create a solution for you.