Thanks for sharing such a nice challenge. Copy and past the next formula into a blank query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdNBCoMwEIXhq0jWQmZejTZ38AaSpTvXnl+tpdiCzQ/ZhPnIkDfJNIVxXuel8dCGxi3aEGXq3rt9vXal/XbOnPpombjM3MOY80/hf1vGBsaejGXGLpXaKJhLyHl/YfenddEFmqYqExyr2FjFEhZMWDDh09Wf03lbZ6nsf8yPQvplZQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Date = _t, Today = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Date", type date}, {"Today", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"Count", each _}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn([Count],"Flag", (x)=>if List.ContainsAll(_[Count][Date],List.Dates(List.Min({x[Date],x[Today]}),Number.Abs(Number.From(x[Today]-x[Date])),#duration(1,0,0,0))) then "Present" else if x[Date]<x[Today] then "Past" else "Future")),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Type", "Date", "Today", "Flag"}, {"Type", "Date", "Today", "Flag"})
in
#"Expanded Custom"
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!
If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
