Conditional GroupBy to retrieve previous values for a contact

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.

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

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