Power Query Grouping Issues

Power Query
1

I have the following query but it is not grouping right. Based on the information provided, I want to mark “Y” for the smallest positive Date_Difference within each group of Email and Campaign Id . If there is a Date_Difference of zero, it should take precedence over other positive numbers. All other entries should be marked as “N”.

Thanks for your help.

Here is the query.
#“Added Custom” = Table.AddColumn(#“Removed Other Columns”, “Date_Difference”, each [Became an MQL Date] - [Interaction Date]),
#“Grouped Rows” = Table.Group(#“Added Custom”, {“Email”, “Campaign Id”}, {{“AllRows”, each _, type table [Email=nullable text, Interaction Date=date, Became an MQL Date=date, Campaign Id=text, Date_Difference=duration]}}),

#“Added Custom1” = Table.AddColumn(#“Grouped Rows”, “MinPositiveDifference”, each let
CurrentGroup = [AllRows],
SortedTable = Table.Sort(Table.SelectRows(CurrentGroup, (inner) =>
inner[Date_Difference] >= #duration(0, 0, 0, 0)),{“Date_Difference”, Order.Ascending})
in
if Table.RowCount(SortedTable) = 0 then null else SortedTable{0}[Date_Difference]),

#“Expanded AllRows” = Table.ExpandTableColumn(#“Added Custom1”, “AllRows”, {“Interaction Date”, “Became an MQL Date”, “Date_Difference”}, {“Interaction Date”, “Became an MQL Date”, “Date_Difference”}),

#“Added Custom2” = Table.AddColumn(#“Expanded AllRows”, “Unique MQL per Contact per Campaign”, each
if [Interaction Date] = null or [Became an MQL Date] = null then “N”
else if [Date_Difference] = [MinPositiveDifference] then “Y” else “N”),

#“Sorted Rows” = Table.Sort(#“Added Custom2”,{{“Email”, Order.Ascending}, {“Campaign Id”, Order.Ascending}}),
#“Changed Type” = Table.TransformColumnTypes(#“Sorted Rows”,{{“MinPositiveDifference”, type number}, {“Unique MQL per Contact per Campaign”, type text}})
in
#“Changed Type”

data.xlsx (9.4 KB)

2

Could you kindly share your ongoing Power BI Desktop file with the Forum members for evaluation? It appears that the Excel file contains some incomplete data rows and columns. If this data is intended for refreshing, could you please confirm that the file is complete or update it as necessary? Thank you.

3

Thank you but the issue is resolved.

The main change here is that the data is now grouped only by Email in the “Grouped Rows” step. Then, in the “Added Custom1” step, it determines the smallest positive Date_Difference (including zero) for each Email . In the “Added Custom2” step, it assigns “Y” if the current row’s Date_Difference equals this smallest positive Date_Difference , and “N” otherwise. This should correctly assign “Y” to only the row with the smallest positive Date_Difference for each Email .

#“Grouped Rows” = Table.Group(#“Added Custom”, {“Email”}, {{“AllRows”, each _, type table [Email=nullable text, Interaction Date=date, Became an MQL Date=date, Campaign Id=text, Date_Difference=duration]}}),

Thank you very much. See attached full query.
Campaign - DateDiffMQL - DO NOT DELETE.txt (2.7 KB)