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 [Work End Date]. I am trying to create a new column that displays the following whole number:

 

If [Work End Date] has a date, then [Work End Date] - [Work Start Date] + 1
If [Work End Date] is "null" then Today's Date - [Work Start Date] + 1

Here is what I have so far, but I keep getting an error message. Thanks for any help you can provide.

= Table.AddColumn("Days in Position", each if [Work End Date] <> null then Duration.Days([Work End Date] - [Work Start Date]) +1, Int64.Type)) else Duration.Days(Date.From(DateTime.LocalNow())) - [Work Start Date]) +1, Int64.Type))

 

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"

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago
    = Table.AddColumn("Days in Position", 
each Duration.Days(([Work End Date]??Date.From(DateTime.LocalNow())) - [Work Start Date]) +1,
 Int64.Type)
  • v-dineshya's avatar
    v-dineshya
    Community Support
    1 year ago

    Hi LFrench ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Can you please try this Power Query M code.

    = Table.AddColumn(Source, "Days in Position", each
    if [Work End Date] <> null then
    Duration.Days([Work End Date] - [Work Start Date]) + 1
    else
    Duration.Days(Date.From(DateTime.LocalNow()) - [Work Start Date]) + 1,
    Int64.Type)

     

    If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

    Thank you

     

     

    • v-dineshya's avatar
      v-dineshya
      Community Support
      1 year ago

      Hi LFrench ,

      If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
      Thanks and Regards

      • v-dineshya's avatar
        v-dineshya
        Community Support
        1 year ago

        Hi LFrench ,

        If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
        Thanks and Regards

  • 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"