@FPastor ,
Very interesting problem. I think @DavieJoe had exactly the right approach, but in his rush out the door to dinner didn’t have the logic quite right. I picked up from him, built out the logic and created a custom function (RowsToKeep) to process the selected data table and remove the rows that don’t meet the logic condition.
Full solution file attached. Here’s the M code for the custom function:
(EnquiriesTable as table) as table =>
let
#"Changed Type" = Table.TransformColumnTypes(
EnquiriesTable,
{
{"LeadID", type text},
{"ContactID", type text},
{"OpportunityID", type text},
{"MYAReference", type text},
{"DateEnquiried", type datetime},
{"Fullname", type text},
{"Email", type text}
}
),
#"Inserted Date" = Table.AddColumn(
#"Changed Type",
"Date",
each DateTime.Date([DateEnquiried]),
type date
),
#"Grouped Rows" = Table.Group(
#"Inserted Date",
{"Date"},
{
{"MinDateTime", each List.Min([DateEnquiried]), type nullable datetime},
{
"Grouping",
each _,
type table [
LeadID = nullable text,
ContactID = nullable text,
OpportunityID = nullable text,
MYAReference = nullable text,
DateEnquiried = nullable datetime,
Fullname = nullable text,
Email = nullable text,
Date = date
]
},
{"NumEnqOnDate", each Table.RowCount(_), Int64.Type}
}
),
#"Expanded Grouping" = Table.ExpandTableColumn(
#"Grouped Rows",
"Grouping",
{"LeadID", "ContactID", "OpportunityID", "MYAReference", "DateEnquiried", "Fullname", "Email"},
{"LeadID", "ContactID", "OpportunityID", "MYAReference", "DateEnquiried", "Fullname", "Email"}
),
#"Added Custom" = Table.AddColumn(
#"Expanded Grouping",
"IsFollowedUp",
each if List.AllTrue({[ContactID] <> "", [OpportunityID] <> ""}) then 1 else 0
),
#"Added Custom2" = Table.AddColumn(
#"Added Custom",
"IsMinRecord",
each if [DateEnquiried] = [MinDateTime] then 1 else 0
),
#"Grouped Rows1" = Table.Group(
#"Added Custom2",
{"Date"},
{
{
"AllData",
each _,
type table [
Date = date,
MinDateTime = nullable datetime,
LeadID = nullable text,
ContactID = nullable text,
OpportunityID = nullable text,
MYAReference = nullable text,
DateEnquiried = nullable datetime,
Fullname = nullable text,
Email = nullable text,
NumEnqOnDate = number,
IsFollowedUp = number,
IsMinRecord = number
]
},
{"SumFollowedUp", each List.Sum([IsFollowedUp]), type number}
}
),
#"Expanded AllData" = Table.ExpandTableColumn(
#"Grouped Rows1",
"AllData",
{
"MinDateTime",
"LeadID",
"ContactID",
"OpportunityID",
"MYAReference",
"DateEnquiried",
"Fullname",
"Email",
"NumEnqOnDate",
"IsFollowedUp",
"IsMinRecord"
},
{
"MinDateTime",
"LeadID",
"ContactID",
"OpportunityID",
"MYAReference",
"DateEnquiried",
"Fullname",
"Email",
"NumEnqOnDate",
"IsFollowedUp",
"IsMinRecord"
}
),
#"Added Custom1" = Table.AddColumn(
#"Expanded AllData",
"Keep",
each
if [SumFollowedUp] > 0 then
(if [IsFollowedUp] = 1 then 1 else 0)
else
(if [IsMinRecord] = 1 then 1 else 0)
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Added Custom1",
{
{"NumEnqOnDate", Int64.Type},
{"IsFollowedUp", Int64.Type},
{"IsMinRecord", Int64.Type},
{"SumFollowedUp", Int64.Type},
{"Keep", Int64.Type}
}
),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Keep] = 1)),
#"Removed Columns" = Table.RemoveColumns(
#"Filtered Rows",
{"NumEnqOnDate", "IsFollowedUp", "IsMinRecord", "SumFollowedUp", "Keep"}
)
in
#"Removed Columns"
Hope this is helpful.
eDNA Forum - PQ Rows to Keep Logic Solution.pbix (109.8 KB)