Web.Contents Called Twice When Triggered via Pipeline

[rohit_motwani]

Hi Community,

I have a Power Query (Dataflow) that sends an email notification using Web.Contents (SendGrid API).

When I manually refresh the Dataflow, the email is sent only once as expected.
But when I trigger the same Dataflow through a pipeline, the email is being sent twice instead of once.

Additional context: This query has a destination connected to it.

Manual refresh → email sent once

Pipeline refresh with destination → email sent twice

How can I ensure that the Web.Contents call is executed only once when the Dataflow is triggered from a Pipeline (with destination)?

let
    AuditTable = LoadToAuditTable,

    // Ensure the column exists
    AuditWithFlag = if Table.HasColumns(AuditTable, "EmailAlreadySent") 
                    then AuditTable
                    else Table.AddColumn(AuditTable, "EmailAlreadySent", each false, type logical),

    // Filter only the row of interest (audit entry)
    RejectRow = Table.SelectRows(AuditWithFlag, each [ComponentName] = "DF_Files_EquityPractice_RejectedRows"),
    Number_RejectedRows = if Table.RowCount(RejectRow) > 0 then RejectRow{0}[AffectedRows] else 0,

    // Check if email has already been sent
    AlreadySentRow = Table.SelectRows(RejectRow, each [EmailAlreadySent] = true),
    ShouldSendEmail = Number_RejectedRows > 0 and Table.RowCount(AlreadySentRow) = 0,

    SendGridResponse =
        if ShouldSendEmail then
            let
                // ✅ Use actual rejected data table instead of audit row
                Source_RejectTable = Table.Buffer(RejectTable),

                // Convert RejectTable → CSV
                csvText = Text.Combine(
                    List.Transform(
                        Table.ToRows(Source_RejectTable),
                        each Text.Combine(
                            List.Transform(_, each if _ = null then """""" else """" & Text.From(_) & """"), 
                            ","
                        )
                    ),
                    "#(lf)"
                ),
                csvWithHeader = 
                    Text.Combine(
                        List.Transform(Table.ColumnNames(Source_RejectTable), each """" & _ & """"), 
                        ","
                    ) 
                    & "#(lf)" & csvText,

                // Encode CSV to Base64
                csvBinary = Text.ToBinary(csvWithHeader),
                csvBase64 = Binary.ToText(csvBinary, BinaryEncoding.Base64),

                // SendGrid body
                body = "{
                      ""personalizations"": [
                        { ""to"": [ { ""email"": ""Recievers_email"" } ],
                          ""subject"": ""Action Required – New data found in [Equity Practice Tool]"" }
                      ],
                      ""from"": { ""email"": ""Senders_email"" },
                      ""content"": [
                        { ""type"": ""text/html"",
                          ""value"": ""Hi Team,<br/><br/>During the processing of [Files.equitypracticetool] of [Equity Practice Tool], new values were found that do not exist in the master record.<br/><br/>Identified new values are added to the attachment.<br/><br/><b>Next Steps:</b><br/>- Review these values and decide whether to add them to the master record or correct them in the file/table.<br/><br/>Regards,<br/>DataHub Service"" }
                      ],
                      ""attachments"": [
                        { ""content"": """ & csvBase64 & """,
                          ""type"": ""text/csv"",
                          ""filename"": ""RejectedRows.csv"" }
                      ]
                    }",

                url = "https://api.sendgrid.com/v3/mail/send",
                apiKey = "API_Key",
                rawResponse = try Web.Contents(
                    url,
                    [
                        Headers = [
                            #"Authorization" = "Bearer " & apiKey,
                            #"Content-Type" = "application/json"
                        ],
                        Content = Text.ToBinary(body),
                        ManualStatusHandling = {202,400,401,403,404,500}
                    ]
                ) otherwise null,
                status = if rawResponse <> null then try Value.Metadata(rawResponse)[Response.Status] otherwise null else null
            in
                status
        else
            null,

    // Rebuild AuditTable safely
    UpdatedAudit = Table.FromRecords(
        List.Transform(
            Table.ToRecords(AuditWithFlag),
            each 
                if [ComponentName] = "DF_Files_EquityPractice_RejectedRows" then
                    let r1 = Record.RemoveFields(_, {"EmailStatus","EmailAlreadySent"}),
                        r2 = Record.AddField(r1, "EmailStatus", Text.From(SendGridResponse)),
                        r3 = Record.AddField(r2, "EmailAlreadySent", ShouldSendEmail)
                    in r3
                else
                    let r1 = Record.RemoveFields(_, {"EmailStatus","EmailAlreadySent"}),
                        r2 = Record.AddField(r1, "EmailStatus", null),
                        r3 = Record.AddField(r2, "EmailAlreadySent", false)
                    in r3
        )
    ),

    #"Changed column type" = Table.TransformColumnTypes(
        UpdatedAudit, 
        {
            {"ParentPipelineRunId", type text}, 
            {"ChildPipelineRunId", type text}, 
            {"ComponentName", type text}, 
            {"ETLId", Int64.Type}, 
            {"AssetId", Int64.Type}, 
            {"AffectedRows", Int64.Type}, 
            {"Status", type text}, 
            {"Exception", type text}, 
            {"StartDate", type datetime}, 
            {"EndDate", type datetime}
        }
    )
in
    #"Changed column type"

[tayloramy · Accepted solution]

Hi @rohit_motwani ,

Power Query can evaluate a query more than once (for things like schema validation and the actual write), so a POST in Web.Contents can fire twice. Since SendGrid’s /v3/mail/send endpoint is not idempotent, each POST becomes a real email. The fix is to move the email send out of the Dataflow and trigger it once at the pipeline level (for example with a Web activity or a small Notebook that runs only on success), or make the send operation idempotent on your side (e.g., log and check a per-run key before sending).

Why multiple evaluations happen: Power Query can issue multiple data-source requests, and Dataflows Gen2 with destinations perform managed steps around the write (managed destination settings, plus staging behaviors described here).

What I’d do

1. In the pipeline, keep your Dataflow activity as-is.
2. Add a dependency-linked step that runs only on success:
   • Option A: Web activity to POST to SendGrid once, passing headers and JSON body.
   • Option B: Notebook (Python) that builds the CSV from your Lakehouse table and sends the email once.

If you absolutely must keep Web.Contents in M, put it in a separate query with no destination, referenced exactly once, and persist an “already sent” flag to storage that you check before sending. This still isn’t bullet-proof against engine re-evaluation, so I recommend moving the send to the pipeline.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.