Forum Discussion
Sort by date and provide unique identifier
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
Hi Anonymous, 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"
1 Reply
Hi Anonymous, 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"