+ Reply to Thread
Results 1 to 5 of 5

How to fix an expression error in power query?

  1. #1
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    505

    How to fix an expression error in power query?

    Hi there,

    I usually extract data from a notepad (.txt) file with no problems. Recently, I have ran into an issue, where times after midnight are not loading/extracting into Excel. If I load the data as normal, those particular cells are blank and empty. When I tried again and looked further, I reviewed under "Transform Data", the cells in question say "Error", with the following message:

    "Expression.Error: We couldn't parse the input provided as a Time value.
    Details:
    24:38:00"

    This has never happened before, is there an added step I need or can do to fix this? So far, in my search, I've read something to do with spaces in the file, but I found none.

    Thank you for your time and much appreciated if you can assist.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: How to fix an expression error in power query?

    Hi Dexter,

    I'm guessing that PQ time thinks all should be between 0 and 24 hours.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    505

    Re: How to fix an expression error in power query?

    Perhaps, I just don't understand why this was never an issue before. I've always loaded the data as normal. Thanks for your response.

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

    Re: How to fix an expression error in power query?

    Agree with Marvin. Excel recognizes time after midnight as having a zero hour. Remove your step to change data type and then do a replace for the 24 to zero. Then you can do your Data Type.
    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

  5. #5
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    505

    Re: How to fix an expression error in power query?

    Currently, the data type is time. How do you isolate just those "Error" cells and what do I change it too?

+ 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. [SOLVED] Runtime Error '3075': Syntax error in the string in the query expression [SQL]
    By leegrj in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-28-2020, 04:11 PM
  2. [SOLVED] Power Query - Expression Error: The Key didn't match any rows in the table
    By Baltimorejack68 in forum Excel General
    Replies: 7
    Last Post: 10-18-2019, 04:24 PM
  3. Expression Error in power query
    By jlblackburn in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-07-2019, 02:48 PM
  4. error in power query & power pivot
    By Baldev Kumar in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-03-2018, 01:34 AM
  5. Syntax Error in Query Expression
    By acmagyar in forum Access Programming / VBA / Macros
    Replies: 0
    Last Post: 07-12-2013, 03:11 PM
  6. Syntaxt Error (missing operator) in query expression!
    By jdgwebdev in forum Access Tables & Databases
    Replies: 3
    Last Post: 02-25-2013, 09:32 AM
  7. 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

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