Create a specific Due Date based on another Date

[jusTodd]

Curious to know if there is a more effective way of doing this in Power Query.

I have a string of dates in which I need to create a due date as the 5th of the following month.

This is what I came up with in a Calendar query ... look forward to any feedback.

let
    Source = #date(2023, 12, 1),
    Custom1 = List.Dates (Source, Number.From(DateTime.LocalNow()) - Number.From(Source), #duration (1,0,0,0)),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Month" = Table.AddColumn(#"Renamed Columns", "NextMonthDate", each Date.AddMonths([Date],1), Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Month",{{"NextMonthDate", type date}}),
    #"Inserted Month1" = Table.AddColumn(#"Changed Type1", "Month", each Date.Month([NextMonthDate]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Month1", "Fifth", each 5),
    #"Inserted Year" = Table.AddColumn(#"Added Custom", "Year", each Date.Year([NextMonthDate]), Int64.Type),
    #"Changed Type2" = Table.TransformColumnTypes(#"Inserted Year",{{"Month", type text}, {"Fifth", type text}, {"Year", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "DueDate", each [Month]&"/"&[Fifth]&"/"&[Year]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"DueDate", type date}})
in
    #"Changed Type3"

[dufoq3 · Accepted solution]

Hi @jusTodd, check this:

let
    Source = #date(2023, 12, 1),
    Dates = List.Dates(Source, Duration.TotalDays(Date.From(DateTime.FixedLocalNow()) - Source) +1, #duration (1,0,0,0)),
    ToTable = Table.FromList(Dates, (x)=> {x}, type table[Date=date]),
    Ad_DueDate = Table.AddColumn(ToTable, "DueDate", each Date.AddMonths(#date(Date.Year([Date]), Date.Month([Date]), 5), 1), type date)
in
    Ad_DueDate

[jusTodd]

This puts it on the 5th of the same month ?

[dufoq3]

Hi, no, my query puts 5th of the following month, but at the beginning there was same month - after few minutes I've edited my query, but you probably copied it before my edit 😉 Copy it again.

[jusTodd]

Yup!  I just got there before you edited.  This works great and is so much more cleaner than my own.  Thanks a bunch!  Really appreciate it.

[dufoq3]

You're welcome. Enjoy 😉

[Jpss]

Hey  @jusTodd 
I recently had to handle a similar scenario that i have documented here. Please check it out.   https://japjeetsodhi.wordpress.com/2024/05/06/generate-dates-between-start-date-and-end-date/

I hope you will find this helpful. 

[jusTodd]

Thanks for the info!  Got your blog on my feedly now and will try this out to see where else I can use it.  🙂