Forum Discussion

Birinder's avatar
Birinder
Helper III
4 years ago
Solved

How to add 2 date columns into a 3rd column.

Hi there, 

I am doing this in excel and it works extremely fine.

 

Suppose I have 2 columns of dates. Date is in MM/DD/YYYY format.

Imagine 1st column has value of 01/01/2016 and 2nd has value of 03/31/2016, I want to create a 3rd column based on this formula: (1st column + 2nd Column)/2 . It returns the value of 02/15/2016.

 

But I can't achieve this by Power Query.

I even tried changing it into date datatype as well as text format.

But it is not adding up.

 

If you have any different approach than addition, Please let me know.

Thank you In Advance.

Need Help
Tips and Tricks
  • Jakinta's avatar
    Jakinta
    4 years ago

    Here is one way to do it.

    The key was to convert dates to numbers.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9Q3MlTSAbGMwcxYHaCwIVzYUN/YAC5sBBc2gqmOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    ToDate = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    #"Changed Type" = Table.TransformColumnTypes(ToDate,{{"Date2", type number}, {"Date1", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date3", each ([Date1]+[Date2])/2, type number),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date1", type date}, {"Date2", type date}, {"Date3", type date}})
in
    #"Changed Type1"

2 Replies

  • Jakinta's avatar
    Jakinta
    Solution Sage
    4 years ago

    Here is one way to do it.

    The key was to convert dates to numbers.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9Q3MlTSAbGMwcxYHaCwIVzYUN/YAC5sBBc2gqmOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    ToDate = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    #"Changed Type" = Table.TransformColumnTypes(ToDate,{{"Date2", type number}, {"Date1", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date3", each ([Date1]+[Date2])/2, type number),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date1", type date}, {"Date2", type date}, {"Date3", type date}})
in
    #"Changed Type1"
  • v-yingjl's avatar
    v-yingjl
    Community Support
    4 years ago

    Hi Birinder ,

    You can try this query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0MzJR0lA2N9YygnNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.From((Number.From([Date1]) + Number.From([Date2])) / 2),type  date)
in
    #"Added Custom"

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.