Help me to restrict the data at dataflow level like using case statement in SQL

[JVKR]

[Martin_D]

Hi @JVKR ,

Are you sure the example is correct and the requirements are correct? The highlighted row does not meet the criterion "when NewValue="closed"". NewValue is "2" in your example.
BR
Martin

[JVKR]

Hi Martin,

my requirement is , i dont want to fetch any records from the source table once the lead is closed.

Example : lead is closed on 20th July 2024 but the lead is updated again on 24th July 2023 which i dont need to pull from the source table.

[Martin_D · Accepted solution]

Hi @JVKR ,
Now I got it. When sorting by the CreatedDate column it becomes quickly obvious that for each lead that already has a "Closed" event you just want to remove all events after the closed event.

You can do this in PowerQuery with these steps:

1. Add a custom column that for each lead contains the Closed date of the lead (or null if the lead is not yet closed) so you can compare CreatedDate against the lead closed date in a filter.
2. Filter the rows and remove all rows with a CreatedDate after the lead closed date (resp. keep all rows with CreatedDate before or at lead closed date).
3. If you need to clean up additional columns, remove the lead closed column again.

Assuming the name of your currently last step in your PowerQuery transformations is "Previous step", then the code you need to add is:

    #"Add column closed date" = Table.AddColumn(
        #"Previous step",
        "ClosedDate",
        (outer) =>
            List.Min(
                Table.SelectRows(
                    #"Previous step",
                    (inner) => 
                        inner[LeadId] = outer[LeadId] and inner[NewValue] = "Closed"
                )[CreatedDate]
            ),
        type datetime
    ),

    // Be aware that the logic in the code describes which rows to keep, 
    // not which rows to remove.
    #"Filtered rows" = Table.SelectRows(
        #"Add column closed date", 
        each [CreatedDate] <= [ClosedDate] or [ClosedDate] = null
    ),

    #"Remove column closed date" = Table.RemoveColumns(
        #"Filtered rows",
        {"ClosedDate"}
    )

then change the return statement according to the name of the new last step:

in
    #"Remove column closed date"

and make sure a comma is added to the end of the previously last step (here called "Previous step").

BR

Martin

[JVKR]

Thanks a lot Martin, its working as expected.

[Martin_D]

Sorry, the LeadId was hardcoded in my original post. I fixed it in the previous message.