+ Reply to Thread
Results 1 to 6 of 6

Expression Error in power query

  1. #1
    Registered User
    Join Date
    09-07-2019
    Location
    Atlanta
    MS-Off Ver
    10
    Posts
    4

    Expression Error in power query

    Good day all,

    I am attempting to add a column using the formula

    today()-[DiscoveryDate]

    I get the following error:

    : The name 'today' wasn't recognized. Make sure it's spelled correctly.

    Is this an Excel syntax error? It works in Power BI.

    Thanks for any assistance.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Expression Error in power query

    today() is not a PQ function. Use DateTime.LocalNow and then format it as Date only.

    https://docs.microsoft.com/en-us/pow...etime-localnow
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    09-07-2019
    Location
    Atlanta
    MS-Off Ver
    10
    Posts
    4

    Re: Expression Error in power query

    Thanks Alan. This solves part of the problem, but when I use DateTime.LocalNow and compare it against [DiscoveryDate], I get an error:

    Expression.Error: We cannot apply operator - to types DateTime and Date.

    Details:
    Operator=-
    Left=9/7/2019 8:20:58 AM
    Right=7/10/2019

    I am totally new to PQ and appreciate your help

    Jim

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Expression Error in power query

    Maybe create a new column and fill it with DateTime.LocalNow. Format it as Date only and then in another new column do your calculation. You can then delete the original DateTime column if it is not needed. PQ is sometimes a little bit quirky.

  5. #5
    Registered User
    Join Date
    09-07-2019
    Location
    Atlanta
    MS-Off Ver
    10
    Posts
    4

    Re: Expression Error in power query

    Thanks Alan! It works.

    A final question.... If the DateTime.localNow column is used in the calculation, if I delete it will it not create an error in the calculated column?

    Jim

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Expression Error in power query

    I don't believe so. Try it and see what happens. You can always go back on the right little window and delete the action if it does not give you the results you desire.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  2. error in power query & power pivot
    By Baldev Kumar in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-03-2018, 01:34 AM
  3. [SOLVED] Syntax error (Missing operator) in query expression
    By lengwer in forum Access Tables & Databases
    Replies: 2
    Last Post: 08-22-2013, 04:00 PM
  4. Syntax Error in Query Expression
    By acmagyar in forum Access Programming / VBA / Macros
    Replies: 0
    Last Post: 07-12-2013, 03:11 PM
  5. Syntaxt Error (missing operator) in query expression!
    By jdgwebdev in forum Access Tables & Databases
    Replies: 3
    Last Post: 02-25-2013, 09:32 AM
  6. Syntax Error (missing Operator) in query expression
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-25-2011, 11:59 AM
  7. [SOLVED] Syntax error (missing operator) in query expression '6 Wescott Rd#
    By shealy in forum Excel General
    Replies: 0
    Last Post: 06-09-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1