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

Yes, check note below my post.

bblackwell3's avatar
bblackwell3
Helper II
2 years ago

Thanks! This is very close to what I am needing. However, I do not want to combine the Applications

into one row. Each Application should have it's own row

 

Text.Combine(a[Application], ", ") }})

  Thanks! This is close to what I am needing. I just don't need the applications combined on one row. Each application should have it's own row

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi, if my code is not what you need - provide expected result based on sample data please.

    • bblackwell3's avatar
      bblackwell3
      Helper II
      2 years ago

      Trying to achieve the below, with each title(application) on one row. I have logic worked out for the latest date column

       

      • dufoq3's avatar
        dufoq3
        Community Champion
        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