Forum Discussion

bblackwell3's avatar
bblackwell3
Helper II
2 years ago
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...
Need Help
Tips and Tricks
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi bblackwell3, 

 

Result

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pSixJTc9MdsvJTM8oUdJRMtU3NNI3MjAy0VGw0DcwhDMNTcFMpVidaCXXovLMPBOgYgt9c6hoLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"Upcoming Dates" = _t]),
    Ad_Dates = Table.AddColumn(Source, "Dates", each List.Transform(Text.Split([Upcoming Dates], ","), (x)=> Date.From(x, "en-US")), type list),
    NextDate = List.First(List.Sort(List.Select(List.Combine(Ad_Dates[Dates]), each _ > Date.From(DateTime.FixedLocalNow())))),
    Tbl = [ a = Table.SelectRows(Ad_Dates, (x)=> List.Contains(x[Dates], NextDate)),
    b = #table(type table[Next Date=text, Next Date App Count=Int64.Type, Applications=text], {{ NextDate, Table.RowCount(a), Text.Combine(a[Application], ", ") }})
  ][b]
in
    Tbl