Forum Discussion
digitalPlay
2 years agoFrequent Visitor
Flagging Non-consecutive overlapping dates
Hello! I hope you guys could help me in solving this, I need to flag rows that have clashing date ranges against the rest of the dates for a specific location. In this example, I am subtracting cur...
spinfuzer
2 years agoSolution Sage
In the approach below, we generate a list of dates for all date ranges. Next we group by location and then combine the list of dates. .
Now we compare Current Row Date List to (Combined Date List without Current Row's Values)
If there is any matching dates then it clashes.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzi/JScxLATINDPUNzPWNDIxMgRxDE30DCxDHTClWJ1rJCFWpkaW+oQFY6aEFIMVAriFEJ0ixMZpiA7gshGOEUGpCirmmmO4FyxoAOcaGUHNNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Location = _t, #"Start Date" = _t, #"End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date List", each List.Dates([Start Date],Duration.TotalDays([End Date]-[Start Date])+1,#duration(1,0,0,0) ) ),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Location"}, {{"Rows", each
let dates = List.Combine(_[Date List])
in
Table.AddColumn(_,
"Status",
(x) => if List.ContainsAny( List.Difference(dates, x[Date List] ), x[Date List]) then "Clash" else "No Clash"
)
}} ),
clash = Table.Combine(#"Grouped Rows"[Rows]),
#"Removed Columns" = Table.RemoveColumns(clash,{"Date List"})
in
#"Removed Columns"