Forum Discussion

qwertyh's avatar
qwertyh
Frequent Visitor
5 years ago
Solved

Adding in a Date Column based on Start Date & End Date

Hello, I am trying to add in an inforce date column which will be the date that drives the calculations for this table.  This will expand the table duplicating each row for each unique month-year ...
Need Help
  • Jakinta's avatar
    Jakinta
    5 years ago

    You can try this...

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjUyVtJRMtQ31TcyMDJAMA2VYnWilZwcQ4yNTIGiRvqGEFEdJZA0iG0EVuHsGGJqYgQUNtM3BooaWsKZQNNiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Policy ID" = _t, #"Start Date" = _t, #"End Date" = _t]),
    DateType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
    DateList = Table.AddColumn(DateType, "Inforce Date", each List.Distinct(List.Transform({Number.From([Start Date]) .. Number.From([End Date])}, each Date.StartOfMonth(Date.From(_))))),
    FINAL = Table.ExpandListColumn(DateList, "Inforce Date")
in
    FINAL
Jakinta's avatar
Jakinta
Icon for Solution Sage rankSolution Sage
5 years ago

You can try this...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjUyVtJRMtQ31TcyMDJAMA2VYnWilZwcQ4yNTIGiRvqGEFEdJZA0iG0EVuHsGGJqYgQUNtM3BooaWsKZQNNiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Policy ID" = _t, #"Start Date" = _t, #"End Date" = _t]),
    DateType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
    DateList = Table.AddColumn(DateType, "Inforce Date", each List.Distinct(List.Transform({Number.From([Start Date]) .. Number.From([End Date])}, each Date.StartOfMonth(Date.From(_))))),
    FINAL = Table.ExpandListColumn(DateList, "Inforce Date")
in
    FINAL
qwertyh's avatar
qwertyh
Frequent Visitor
5 years ago

Both this response & Ed Han's response solve my question. I chose this because of it's simplicity and beauty. EdHan's response is much easier to follow and I love the instruction and explanation given to help understand the code.

Thank you both!

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Great qwertyh - glad you got a solution that works. 

    For future reference, you can mark multiple responses as "the solution" to a thread should you find that more than one response works.

    I tend to go for a bit more verbose and formatted code, which takes up a few more lines, but easier for me to walk through. 😁