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 that falls between the start and end date inclusively and always be the 1st of each month.

For example, with this table,

Policy IDStart DateEnd DateOthers Column etc..
ABC5231/5/20201/5/2021 
BAT3252/1/20215/20/2022 
CAT5426/3/20196/3/2020 

I would like to have this:

Policy IDStart DateEnd DateInforce DateOthers Column etc..
ABC5231/5/20201/5/20211/1/2020 
ABC5231/5/20201/5/20212/1/2020 
ABC5231/5/20201/5/20213/1/2020 
ABC5231/5/20201/5/20214/1/2020 
ABC5231/5/20201/5/20215/1/2020 
ABC5231/5/20201/5/20216/1/2020 
ABC5231/5/20201/5/20217/1/2020 
ABC5231/5/20201/5/20218/1/2020 
ABC5231/5/20201/5/20219/1/2020 
ABC5231/5/20201/5/202110/1/2020 
ABC5231/5/20201/5/202111/1/2020 
ABC5231/5/20201/5/202112/1/2020 
ABC5231/1/20201/1/20211/1/2021 
BAT3252/1/20215/20/20222/1/2021 
BAT3252/1/20215/20/20223/1/2021 

etc..

Thanks for the help.

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

4 Replies

  • 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. 😁

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

    Try this qwertyh 

    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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
    #"Added Date List" = 
        Table.AddColumn(
            #"Changed Type", 
            "Date List", 
            each 
                let
                    varStartDate = Date.StartOfMonth([Start Date]),
                    varEndDate = Date.StartOfMonth([End Date]) + #duration(1,0,0,0),
                    varDates = List.Dates(varStartDate, Duration.TotalDays(varEndDate - varStartDate), #duration(1,0,0,0))
                in
                    List.Select(varDates, each _ = Date.StartOfMonth(_))
        ),
    #"Expanded Date List" = Table.ExpandListColumn(#"Added Date List", "Date List")
in
    #"Expanded Date List"

     

    It does this:

    1. Gets your Start Date and finds the first day of the nonth.
    2. Gets your End Date and finds the first day of that month, then adds 1 day.
    3. Creates a list of all dates in between.
    4. Keeps only those dates in that are the start of the month.
    5. Expands them to a new row.

    Here is some of the data:

     

    How to use M code provided in a blank query:
    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done
    5) See this article if you need help using this M code in your model.