Sort by date and provide unique identifier

[Kizz]

Hi,

My table consists of a range of activities with a huge range of start and end dates. I have written this piece of code to determine which activities are occurring in the 6 weeks (It seems to work but haven't done full testing):

if (((DateTime.IsInNextNHours([Start date], 1008) = true) or (DateTime.Date( DateTime.LocalNow() ) >= [Start date] and DateTime.Date( DateTime.LocalNow() ) <= [End date] )) and (DateTime.IsInNextNHours([End date], 1008) = true)) or ((DateTime.Date( DateTime.LocalNow() ) >= [Start date] and DateTime.Date( DateTime.LocalNow() ) <= [End date] )) then "6 week" else "out"

What I would ultimately like to do though is then give these activities a unique number that increases by 1, starting with the earliest Start date.

For example, the data would look something like:

Activity	Start date	End date	Within 6 weeks	Unique ID
Activity one	8/3/2024	15/3/2024	6 week	2
Activity two	10/3/2022	10/4/2022	out	N/A
Activity three	1/3/2024	3/3/2024	6 week	1
Activity four	17/3/2024	30/3/2024	6 week	3

Some help would be wonderful!

Cheers,

K

[dufoq3 · Accepted solution]

Hi @Kizz, try this:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckwuySzLLKlUyM9LVdJRstA31jcyMDIBMg1NEWwzhfLU1GylWB0kDSXl+SBVBhBVRhC2CYydX1qCpjyjKBVkgyHCVGP8FqTllxaBNJgj6TDA1BILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Activity = _t, #"Start date" = _t, #"End date" = _t, #"Within 6 weeks" = _t]),
    #"v1_Changed Type" = Table.TransformColumnTypes(Source,{{"Start date", type date}, {"End date", type date}}, "sk-SK"),
    #"v1_Added Index" = Table.AddIndexColumn(#"v1_Changed Type", "Index", 0, 1, Int64.Type),
    #"v1_Grouped Rows" = Table.Group(#"v1_Added Index", {"Within 6 weeks"}, {{"All", each Table.AddIndexColumn(Table.Sort(_, {"Start date", Order.Ascending}), "Unique ID", 1, 1, Int64.Type) , type table}}),
    v1_CombinedTables = Table.Combine(#"v1_Grouped Rows"[All]),
    v1_ReplaceValues = Table.ReplaceValue( v1_CombinedTables,
      each [Within 6 weeks],
      null,
      (x,y,z)=> if y = "out" then null else x,
      {"Unique ID"} ),
    #"v1_Sorted Rows" = Table.Sort(v1_ReplaceValues,{{"Index", Order.Ascending}}),
    #"v1_Removed Columns" = Table.RemoveColumns(#"v1_Sorted Rows",{"Index"})
in
    #"v1_Removed Columns"