Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
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
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
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.
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:
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
Thanks a lot Martin, its working as expected.
Sorry, the LeadId was hardcoded in my original post. I fixed it in the previous message.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
6 | |
6 | |
3 | |
2 | |
2 |
User | Count |
---|---|
6 | |
5 | |
4 | |
4 | |
3 |