Forum Discussion
Solved
Create a specific Due Date based on another Date
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"
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
7 Replies
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_DueDateHey 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.Thanks for the info! Got your blog on my feedly now and will try this out to see where else I can use it. 🙂
