Forum Discussion
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 (...
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
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