Forum Discussion

jbrooi's avatar
jbrooi
Helper I
2 years ago
Solved

Conditional GroupBy to retrieve previous values for a contact

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 (...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    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
AlienSx's avatar
AlienSx
Super User
2 years ago
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's avatar
jbrooi
Helper I
2 years ago

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's avatar
    AlienSx
    Super User
    2 years ago

    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