Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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"
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.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.