Calculating estimated date using numerals

[lucaslobri]

Hi guys,

I am diving into Power Query in the last few days. I already have expertise in Excel, but I've been facing some difficulties to understand how to work using Dates in Query. I've tried to search the solution in other topics - did not find anything related.

The problem is: I'm working to display an estimated payment date, calculated by summing the receipt date + the leadtime from the payment term.

I tried to create a Custom Column using the following scheme, Receipt DT (date) + Payment LT (num) = Payment DT (date). Receipt DT and Payment LT columns are already available in my Query, but as the first is a date and the second is a numeral, I'm not achieving any progress cause I cannot merge both values.  Thats the first issue.

After that, I also need to create another Column to manipulate the returned dates because I only have 3 different days that Payment DT (date) could display - 5th, 15th and 25th of each month. For example, we receive anything between day 6 and 14, we're going to pay it on day 15. Bellow you can see the logic using Excel references just to be clear.

if DAY(return) > 0 and DAY(return) < 5 then #date(YEAR(return), MONTH(return), 5)

else if DAY(return) > 4 and DAY(return) < 15 then #date(YEAR(return), MONTH(return), 15) 

else if DAY(return) > 14 and DAY(return) < 25 then #date(YEAR(return), MONTH(return), 25) 

else if DAY(return) > 24 and DAY(return) < 32 then #date(YEAR(return), MONTH(return), 1.5) 

else null

(Thats not the correct code, I know, just wrote how do I can reach the result I want in Excel)

I would appreciate it if you could offer me a suggestion or a parallel topic where I could learn more about it.

Thank you in advance,

Lucas

PS: I'm not sharing any file because I think its not necessary, and it contains some confidential data.

[Daryl-Lynch-Bzy]

Hi @lucaslobri - to add "number" to the date, you need to convert it to a duration.  In the following, I show how the number can be added into a duration statement.  Please note the syntax for duration > #duration( days, hours, minutes, seconds ) 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJS0lEyUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Wrong", each [Date] + [Number]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Right", each [Date] + #duration([Number],0,0,0))
in
    #"Added Custom1"

For the second require please use following functions with the [Return] date (make sure this is a date):
Date.Day - PowerQuery M | Microsoft Learn

Date.Month - PowerQuery M | Microsoft Learn

Date.Year - PowerQuery M | Microsoft Learn

Note the use of #date( year, month, day) is correct, but please check the 1.5 in the last condition.