Forum Discussion

jbrooi's avatar
jbrooi
Icon for Helper I rankHelper 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
VN999's avatar
VN999
Icon for Resolver I rankResolver I
2 years ago

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.