Custom Function Not Calculating

[Mknott93]

Hi all,

Apologies for the inexperience. I'm sure this is a super trivial and easy fix but I did some power query training a couple of years ago in my previous company and found it massively useful. I had no issues creating custom columns using various functions, but all of a sudden, now I've joined a new organisation, I'm trying to do some basic date calculations in a custom column and it just returns a 'Function' value as below. I'm pretty sure this is likely just a setup issue but for the life of me I can't find the fix online. Does anyone know how to fix this? I really don't understand why this isn't displaying the current DateTime.

Thanks in advance.

[KNP]

Can you please include the code you are using and what you expect it to return?

It will likely be easy to fix but there's not enough info.

[Mknott93]

Hi KNP. Thanks for taking the time to get back to me.

That code at the moment is just 'DateTime.LocalNow'.

I wanted to create a formula which gives me the difference in days between a start and end date. However, after writing out the code, this error flashed up so I started deleting parts of the code until I figured out what was causing the problem.

[KNP]

Hi @Mknott93, 

See below gif.

Here is the complete code. Paste this into the advanced editor of a blank query.

let
  Source = #date(2021, 11, 16),
  #"Converted to Table" = #table(1, {{Source}}),
  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "StartDate"}}),
  #"Added Custom" = Table.AddColumn(#"Renamed Columns", "EndDate", each #date(2021, 11, 18)),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Added Custom",
    {{"StartDate", type date}, {"EndDate", type date}}
  ),
  #"Inserted Date Subtraction" = Table.AddColumn(
    #"Changed Type",
    "Subtraction",
    each Duration.Days([EndDate] - [StartDate]),
    Int64.Type
  )
in
  #"Inserted Date Subtraction"

 I hope this helps.

[Mknott93]

Thanks for your help. I actually think I was just missing the brackets in my formula which is embarrasing, but this is a very useful code for when I need to do the date subtraction. Thanks again.

[KNP]

You're welcome.

You described the problem in your reply as wanting the difference between two dates, which is what I provided.

Also, be careful with the DateTime.LocalNow() depending on your query structure it can change during successive calls in the same refresh, producing unexpected results.

Typically you'd be better off with DateTime.FixedLocalNow().

[ronrsnfld · Accepted solution]

Just change it to:

=DateTime.LocalNow()

[Mknott93]

Wow, that's a little embarrasing. I've done this so many times, no idea how I missed that. Thanks for this.