Forum Discussion

lucaslobri's avatar
lucaslobri
New Member
3 years ago

Calculating estimated date using numerals

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...
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.