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