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

[qwertyh]

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 ID	Start Date	End Date	Others Column etc..
ABC523	1/5/2020	1/5/2021
BAT325	2/1/2021	5/20/2022
CAT542	6/3/2019	6/3/2020

I would like to have this:

Policy ID	Start Date	End Date	Inforce Date	Others Column etc..
ABC523	1/5/2020	1/5/2021	1/1/2020
ABC523	1/5/2020	1/5/2021	2/1/2020
ABC523	1/5/2020	1/5/2021	3/1/2020
ABC523	1/5/2020	1/5/2021	4/1/2020
ABC523	1/5/2020	1/5/2021	5/1/2020
ABC523	1/5/2020	1/5/2021	6/1/2020
ABC523	1/5/2020	1/5/2021	7/1/2020
ABC523	1/5/2020	1/5/2021	8/1/2020
ABC523	1/5/2020	1/5/2021	9/1/2020
ABC523	1/5/2020	1/5/2021	10/1/2020
ABC523	1/5/2020	1/5/2021	11/1/2020
ABC523	1/5/2020	1/5/2021	12/1/2020
ABC523	1/1/2020	1/1/2021	1/1/2021
BAT325	2/1/2021	5/20/2022	2/1/2021
BAT325	2/1/2021	5/20/2022	3/1/2021

etc..

Thanks for the help.

[edhans]

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.

[Jakinta · Accepted solution]

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]

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]

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