Forum Discussion

LFrench's avatar
LFrench
Frequent Visitor
1 year ago
Solved

If then statement with embedded calculation

I feel like I am close, and I can make each column work seperately, but now I need them to work together if possible. Where is my formula going wrong?   I have two columns, [Work Start Date] and ...
Need Help
  • SundarRaj's avatar
    SundarRaj
    1 year ago

    Hi LFrench , another solution that you could look at. I'll attach the images and the M code used.

    Here's the code:
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type datetime}, {"EndDate", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.From([StartDate]) is date then Duration.Days([StartDate] - [EndDate]) + 1
    else if [EndDate] = null then Duration.Days(Date.From(DateTime.FixedLocalNow()) - [StartDate]) + 1 else null)
    in
    #"Added Custom"

SundarRaj's avatar
SundarRaj
Super User
1 year ago

Hi LFrench , another solution that you could look at. I'll attach the images and the M code used.

Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type datetime}, {"EndDate", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Date.From([StartDate]) is date then Duration.Days([StartDate] - [EndDate]) + 1
else if [EndDate] = null then Duration.Days(Date.From(DateTime.FixedLocalNow()) - [StartDate]) + 1 else null)
in
#"Added Custom"