Forum Discussion
Solved
Determining Next Date
Hey All. I have a dataset with an Application name column and a, Upcoming dates column with dates like below: Some applications may have one date in the Upcoming dates column or multiple dates...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pSixJTc9MdsvJTM8oUdJRMtU3NNI3MjAy0VGw0DcwhDMNTcFMpVidaCXXovLMPBOgYgt9c4RoSGoxyAADc4Q2EBumLxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"Upcoming Dates" = _t]), Ad_NextDate = Table.AddColumn(Source, "Next Date", each [ a = List.Transform(Text.Split([Upcoming Dates], ","), (x)=> Date.From(x, "en-US")), b = List.Select(a, (x)=> x > Date.From(DateTime.FixedLocalNow())), c = List.Min(b) ][c], type date) in Ad_NextDate
Trying to achieve the below, with each title(application) on one row. I have logic worked out for the latest date column
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pSixJTc9MdsvJTM8oUdJRMtU3NNI3MjAy0VGw0DcwhDMNTcFMpVidaCXXovLMPBOgYgt9c4RoSGoxyAADc4Q2EBumLxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"Upcoming Dates" = _t]),
Ad_NextDate = Table.AddColumn(Source, "Next Date", each
[ a = List.Transform(Text.Split([Upcoming Dates], ","), (x)=> Date.From(x, "en-US")),
b = List.Select(a, (x)=> x > Date.From(DateTime.FixedLocalNow())),
c = List.Min(b)
][c], type date)
in
Ad_NextDateThanks again for the help. This solution works for my issue
You're welcome 😉
Sorry, for the late follow-up. Would there be a reason for the Next Date logic not to acurately see am item with today's date as being the next date? I have a record where the "next" date is today, but the logic does not count the records? See the 8/5/2024 record below