Forum Discussion

Malfi's avatar
Malfi
New Member
1 year ago
Solved

Issue with Promoting Headers in Power Query (Exchange Connector)

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!



 

Need Help
  • jaineshp's avatar
    jaineshp
    1 year ago

    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

5 Replies

  • jaineshp's avatar
    jaineshp
    Memorable Member
    1 year ago

    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

  • ChielFaber's avatar
    ChielFaber
    Super User
    1 year ago


    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

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    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's avatar
    v-lgarikapat
    Community Support
    1 year ago

    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.