If then statement with embedded calculation

[LFrench]

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))

[SundarRaj · Accepted solution]

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]

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]

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]

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]

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

[lbendlin]

= Table.AddColumn("Days in Position", 
each Duration.Days(([Work End Date]??Date.From(DateTime.LocalNow())) - [Work Start Date]) +1,
 Int64.Type)