Forum Discussion
Issue with Promoting Headers in Power Query (Exchange Connector)
Hey Malfi,
I've dealt with this exact issue before. The changing DateTime values definitely mess up the header promotion. Here's what worked for me:
Solution:
- Skip rows dynamically first - Use Table.Skip(YourTable, 1) or Table.Skip(YourTable, 2) before promoting headers. This bypasses the DateTime row that's causing the offset.
- Find header row programmatically - Add this step to locate your actual data headers:
= Table.Skip(Source, List.PositionOf(Table.Column(Source, "Column1"), "YourExpectedHeaderName")) - Use Table.PromoteHeaders after the skip - Once you've skipped past the DateTime rows, the promotion should work consistently.
This eliminates the DateTime dependency completely. I've been using this approach for months with daily email refreshes and haven't had issues since.
The key is identifying the consistent row position of your headers relative to the email structure, not the DateTime values.
Fixed? ✓ Mark it • Share it • Help others!
Best Regards,
Jainesh Poojara | Power BI Developer
If I'm reading your question right the problem is as depicted in the picture above (where column 73, 74 are most likely blank?)
With the above dummy data I managed to get it to work using the following script:
let
// Replace 'Bron' with your actual table step
Bron = bron,
// 1) Extract the first 2 rows (these contain the header candidates)
HeaderRows = Table.FirstN(Bron, 2),
// 2) Row 1 and Row 2 as records
R1Rec = HeaderRows{0},
R2Rec = HeaderRows{1},
// 3) Convert both records to lists so we can access values by index
R1 = Record.ToList(R1Rec),
R2 = Record.ToList(R2Rec),
// 4) Build the new header list:
// - First column: row 2 value
// - Second column: row 2 value
// - Third column: row 1 value
NewHeadersRaw = { R2{0}, R2{1}, R1{2} },
NewHeaders = List.Transform(NewHeadersRaw, each Text.From(_)),
// 5) Get the data rows (skip the first 2 header rows)
DataRows = Table.Skip(Bron, 2),
// 6) Rename the data table’s columns with the new header list
Result = Table.RenameColumns(
DataRows,
List.Zip({ Table.ColumnNames(DataRows), NewHeaders }),
MissingField.Ignore
)
in
Result
the output:
Could you check if this is working for you?
Regards,
Chiel