Creation of Flag in Power Query based on Dates

[eng_123]

Would require help to create a flag column to identify Past, Present and Future based on the Date in Power Query.

The data would come across something similar to this:

Type	Date	Today
Level 1	10/07/2024	10/10/2024
Level 1	11/07/2024	10/10/2024
Level 1	26/09/2024	10/10/2024
Level 1	29/09/2024	10/10/2024
Level 1	30/09/2024	10/10/2024
Level 1	1/10/2024	10/10/2024
Level 1	2/10/2024	10/10/2024
Level 1	7/10/2024	10/10/2024
Level 1	8/10/2024	10/10/2024
Level 1	9/10/2024	10/10/2024
Level 1	10/10/2024	10/10/2024
Level 1	11/10/2024	10/10/2024
Level 1	15/10/2024	10/10/2024
Level 1	16/10/2024	10/10/2024
Level 1	4/12/2024	10/10/2024
Level 1	5/12/2024	10/10/2024
Level 2	30/09/2024	10/10/2024
Level 2	1/10/2024	10/10/2024
Level 2	9/10/2024	10/10/2024
Level 2	10/10/2024	10/10/2024
Level 2	11/10/2024	10/10/2024
Level 2	12/10/2024	10/10/2024
Level 2	31/12/2024	10/10/2024
Level 2	1/01/2025	10/10/2024

Create additonal flag column in power query

Past= Does not fit in latest cycle of continous day in between todays dates and are previous dates

Present= Fits in latest cycle of continous day in between todays dates 

Future= Does not fit in latest cycle of continous day in between todays dates and are future dates

Type	Date	Today	Type Flag
Level 1	10/07/2024	10/10/2024	Past
Level 1	11/07/2024	10/10/2024	Past
Level 1	26/09/2024	10/10/2024	Past
Level 1	29/09/2024	10/10/2024	Past
Level 1	30/09/2024	10/10/2024	Past
Level 1	1/10/2024	10/10/2024	Past
Level 1	2/10/2024	10/10/2024	Past
Level 1	7/10/2024	10/10/2024	Present
Level 1	8/10/2024	10/10/2024	Present
Level 1	9/10/2024	10/10/2024	Present
Level 1	10/10/2024	10/10/2024	Present
Level 1	11/10/2024	10/10/2024	Present
Level 1	15/10/2024	10/10/2024	Future
Level 1	16/10/2024	10/10/2024	Future
Level 1	4/12/2024	10/10/2024	Future
Level 1	5/12/2024	10/10/2024	Future
Level 2	30/09/2024	10/10/2024	Past
Level 2	1/10/2024	10/10/2024	Past
Level 2	9/10/2024	10/10/2024	Present
Level 2	10/10/2024	10/10/2024	Present
Level 2	11/10/2024	10/10/2024	Present
Level 2	12/10/2024	10/10/2024	Present
Level 2	31/12/2024	10/10/2024	Future
Level 2	1/01/2025	10/10/2024	Future

[eng_123]

Thank you

[AlienSx]

let
    Source = your_table,
    type_flag = (tbl) => 
        (
            (w) => Table.AddColumn(tbl, "Type Flag", each w)
        )(if List.Contains(tbl[Date], tbl[Today]{0}) then "Present" else if tbl[Date]{0} < tbl[Today]{0} then "Past" else "Future"),
    idx = Table.AddIndexColumn(Source, "idx"), 
    group = Table.Group(
        idx, 
        {"Type", "Date", "idx"}, 
        {"x", type_flag}, 
        GroupKind.Local, 
        (s, c) => Number.From(s[Type] <> c[Type] or Duration.Days(c[Date] - s[Date]) <> c[idx] - s[idx])),
    result = Table.Combine(group[x])
in
    result

[Omid_Motamedise · Accepted solution]

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!