Conditional GroupBy to retrieve previous values for a contact

[jbrooi]

Hi, I've been playing around with this, and searching here, but can't make it work.

Want to group by Contact and fill in blank spots for Source and Medium, if previous CreateDate is within 90 days (max 90 days back). 

Data sample: 

Contact ID	CreateDate	Source	Medium
1234	23-5-2024	LinkedIn	cpc
1234	13-5-2024
1234	18-12-2023	Google	organic
7890	28-6-2024
7890	21-6-2024	Indeed	cpc
7890	10-3-2024	Google	organic
7890	1-2-2024	Google	organic

Required result:

Contact ID	CreateDate	Source	Medium
1234	23-5-2024	LinkedIn	cpc
1234	13-5-2024
1234	18-12-2023	Google	organic
7890	28-6-2024	Indeed	cpc
7890	21-6-2024	Indeed	cpc
7890	10-3-2024	Google	organic
7890	1-2-2024	Google	organic

(I have replaced the blank spots with nulls.) 

So the second line for Contact 1234 is not filled because the previous Source is from more than 90 days back. 

The 2nd line for contact 7890 is filled because a previous source and medium are there and is within 90 days. 

thanks 

[dufoq3]

Hi @jbrooi, different approach here:

with this solution it is not mandantory to have sorted columns via [Contact ID] and [CreateDate]

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTIy1jXVNTIwArF9MvOyU1M884DM5IJkpVgduCpDJFUKYIwsaaFraASSNQZy3PPz03NSgYz8ovTEvEyIKeYWlgYguyx0zTBNgUkaIiQ981JSU1OQnAFVY2igawxTg88iQ10j3MpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contact ID" = _t, CreateDate = _t, Source = _t, Medium = _t]),
    // You probably do not need this step.
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if Text.Trim(_) = "" then null else _),
    ChangedType = Table.TransformColumnTypes(ReplaceBlankToNull,{{"Contact ID", Int64.Type}, {"CreateDate", type date}, {"Source", type text}, {"Medium", type text}}),

    fn_SourceMedium = 
      (myTable as table)=>
      let
        // _Detail = GroupedRows{[#"Contact ID"=7890]}[All],
        _Detail = myTable,

        RemovedOtherColumnsInner = Table.SelectColumns(_Detail,{"CreateDate", "Source", "Medium"}),
        SortedRowsInner = Table.Sort(RemovedOtherColumnsInner,{{"CreateDate", Order.Descending}}),
        BufferedInner = Table.Buffer(SortedRowsInner),
        GeneratedSourceMedium = List.Generate(
            ()=> [ x = 0, 
                  dtCurrent = BufferedInner{x}[CreateDate], 
                  dtPrev = BufferedInner{x+1}[CreateDate], 
                  source = try if Duration.TotalDays(dtCurrent - dtPrev) <= 90 and BufferedInner{x+1}[Source] <> null then BufferedInner{x+1}[Source] else BufferedInner{x}[Source] otherwise BufferedInner{x}[Source],
                  medium = try if Duration.TotalDays(dtCurrent - dtPrev) <= 90 and BufferedInner{x+1}[Medium] <> null then BufferedInner{x+1}[Medium] else BufferedInner{x}[Medium] otherwise BufferedInner{x}[Medium] ],
            each [x] < Table.RowCount(BufferedInner),
            each [ x = [x]+1, 
                  dtCurrent = BufferedInner{x}[CreateDate], 
                  dtPrev = BufferedInner{x+1}[CreateDate],  
                  source = try if Duration.TotalDays(dtCurrent - dtPrev) <= 90 and BufferedInner{x+1}[Source] <> null then BufferedInner{x+1}[Source] else BufferedInner{x}[Source] otherwise BufferedInner{x}[Source],
                  medium = try if Duration.TotalDays(dtCurrent - dtPrev) <= 90 and BufferedInner{x+1}[Medium] <> null then BufferedInner{x+1}[Medium] else BufferedInner{x}[Medium] otherwise BufferedInner{x}[Medium] ],
            each [Source = [source], Medium = [medium]]
    ),
        TblFromRecords = Table.FromRecords(GeneratedSourceMedium, type table[Source=text, Medium=text]),
        ToTable = [ a = Table.RemoveColumns(_Detail, {"Source", "Medium"}),
            b = Table.FromColumns(Table.ToColumns(a) & Table.ToColumns(TblFromRecords), Value.Type(a & TblFromRecords))
          ][b]
      in
          ToTable,

    GroupedRows = Table.Group(ChangedType, {"Contact ID"}, {{"All", fn_SourceMedium, type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in 
    CombinedAll

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    sort = Table.Sort(Source,{{"Contact ID", Order.Ascending}, {"CreateDate", Order.Ascending}}),
    group = Table.Group(
        sort, 
        {"CreateDate", "Source"},
        {"x", (x) => Table.FillDown(x, {"Source", "Medium"})},
        GroupKind.Local, 
        (s, c) => Number.From(c[Source] <> null or Duration.Days(c[CreateDate] - s[CreateDate]) > 90)
    ),
    combine = Table.Combine(group[x])
in
    combine

[jbrooi]

Thanks! Only thing it's filling down Sources & Mediums from one Contact to the other.

Probably needs an additional statement for looking at the same Contact ID only to fill down? 

[AlienSx · Accepted solution]

@jbrooi  yes, I forgot abt Contact ID. Try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    sort = Table.Sort(Source,{{"Contact ID", Order.Ascending}, {"CreateDate", Order.Ascending}}),
    group = Table.Group(
        sort, 
        {"Contact ID", "CreateDate", "Source"},
        {"x", (x) => Table.FillDown(x, {"Source", "Medium"})},
        GroupKind.Local, 
        (s, c) => Number.From(
            s[Contact ID] <> c[Contact ID] or 
            c[Source] <> null or 
            Duration.Days(c[CreateDate] - s[CreateDate]) > 90
        )
    ),
    combine = Table.Combine(group[x])
in
    combine

[VN999]

Add custom columns to calculate the previous non-blank values within 90 days:

Custom Column for source:

SourceFilled =
if [Source] <> null then [Source]
else if List.Max(
Table.SelectRows(
#"Added Custom",
each [Contact ID] = [Contact ID] and [CreateDate] < [CreateDate] and [CreateDate] >= Date.AddDays([CreateDate], -90)
)[CreateDate]
) = [CreateDate] then List.First(
Table.SelectRows(
#"Added Custom",
each [Contact ID] = [Contact ID] and [CreateDate] < [CreateDate] and [CreateDate] >= Date.AddDays([CreateDate], -90)
)[Source]
)
else null

Custom Column for medium :

MediumFilled =
if [Medium] <> null then [Medium]
else if List.Max(
Table.SelectRows(
#"Added Custom",
each [Contact ID] = [Contact ID] and [CreateDate] < [CreateDate] and [CreateDate] >= Date.AddDays([CreateDate], -90)
)[CreateDate]
) = [CreateDate] then List.First(
Table.SelectRows(
#"Added Custom",
each [Contact ID] = [Contact ID] and [CreateDate] < [CreateDate] and [CreateDate] >= Date.AddDays([CreateDate], -90)
)[Medium]
)
else null

After adding these custom columns, apply the changes in Power Query and Load the transformed data into Power BI.