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"

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

    • 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