Forum Discussion
FInd header row dynamically in Query
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.
Try this one might help you
HeaderRowIndex = List.PositionOf(
Table.Column(Sheet, "Column1"),
"KPI ID"
)Anonymous
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"
5 Replies
Anonymous 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.
Try this one might help you
HeaderRowIndex = List.PositionOf(
Table.Column(Sheet, "Column1"),
"KPI ID"
)Anonymous
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"
