Issue with Promoting Headers in Power Query (Exchange Connector)

[Malfi]

Hello Everyone,

I’m currently receiving data by email every day, and I use the Microsoft Exchange connector in Power BI (via Power Query) to pull this data for my analysis.

The issue I’m facing is with promoting headers. When I invoke my function, most of the columns end up with a row offset, leaving a gap before the actual data. I’ve tried using the Promote Headers function, but since the “Date Time Received” value changes every day, it causes errors during refresh.

Has anyone encountered this before? Do you have a simple way to handle this so that the headers are promoted correctly each time, regardless of the daily variation?

Thanks in advance for your help!

[dufoq3]

Hi @Malfi, another solution with dynamic rename:

Before

After

let
    Source = Expression.Evaluate("Table.FromRows(List.Transform(Text.Split(""1-Jun-28;1-Jul-28;8/18/2025 7:01:05 AM|0;0;8/18/2025 7:01:05 AM|0;0;8/18/2025 7:01:05 AM"",""|""), each Text.Split(_, "";"")), {""Column73"",""Column74"",""DateTimeReceived""})", #shared),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    DynamicRename = Table.RenameColumns(PromotedHeaders,{{Table.ColumnNames(PromotedHeaders){List.PositionOf(Table.ColumnNames(Source), "DateTimeReceived")}, "DateTimeReceived"}})
in
    DynamicRename

[v-lgarikapat]

Hi @Malfi ,

Thank you for reaching out to the Microsoft fabric community forum.

@ChielFaber , @jaineshp 

Thanks for your prompt response

@Malfi , 

I wanted to follow up and confirm whether you’ve had the opportunity to review the information   provided by @ChielFaber , @jaineshp  Should you have any questions or require further clarification, please don't hesitate to reach out.

We appreciate your engagement and thank you for being an active part of the community.

Best Regards,

Lakshmi.

[ChielFaber]

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

[jaineshp · Accepted solution]

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

[Malfi]

Hey @jaineshp 

Thanks a lot It worked perfectly 🙂

@ChielFaber  Thanks a lot for your answer.

Best Malfi