Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
3 | |
3 | |
2 | |
2 | |
2 |