+ Reply to Thread
Results 1 to 8 of 8

Power Query error handling in date column

  1. #1
    Registered User
    Join Date
    07-27-2020
    Location
    Manchester, UK
    MS-Off Ver
    Office 365
    Posts
    21

    Power Query error handling in date column

    I'm sure this must be straightforward but I cannot get my head around it.

    I have a lengthy Power Query that includes a column that includes both dates and text. I need the column to be formatted explicitly as a date column (so that the output table can be selected using drop down month/year filters) but while still returning the text fields as they originally appear instead of errors/blanks.

    The text fields vary so they needs to be based on the original content. I've tried to work out how to do it using a 'try' statement but I'm not having any luck.

    Is anyone able to point me in the right direction please?
    Attached Files Attached Files

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Power Query error handling in date column

    You can't have a Date type column with text in it - that's a contradiction in terms. Your source table already uses Month/year filters as well as the text values, so I'm not sure what it is you are missing?
    Rory

  3. #3
    Registered User
    Join Date
    07-27-2020
    Location
    Manchester, UK
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Power Query error handling in date column

    Thanks Rory.

    The column in the attached file was just an example. The actual file include two dozen queries to generate this data, and the date/text output is derived from a large string of text. I've included a slightly different example to reflect this, whereby the 'date' column is extracted using a delimiter.

    If the data was directly derived from dates then you are correct that the filters apply automatically, but because the output is driven by PQ, I can't work out a way to replicate this output, other than listing dates as text (which removes the ability to correctly filter) or by returning an error (which excludes any text variables).
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Power Query error handling in date column

    Change the last step to:

    = Table.TransformColumns(#"Changed Type", {{"Due date", each try Date.FromText(Text.AfterDelimiter(_, "TEST")) otherwise Text.AfterDelimiter(_, "TEST"), type any}})

    so you get real dates and a column type of Any. Then format the column in the result table as date.

  5. #5
    Registered User
    Join Date
    07-27-2020
    Location
    Manchester, UK
    MS-Off Ver
    Office 365
    Posts
    21

    Power Query error handling in date column

    Continuation of the above, so post copied from now closed duplicate thread. Continue here, please.

    Morning all - seriously hoping that someone can help with a PQ issue that is causing me a lot of headaches...

    I run a SharePoint site containing >5000 files, and each file contains custom Properties that inform me of the status of each respective file. Every day I run a PQ from a separate file that queries and compiles the properties from each of these files and builds reports from the back of it. As the data I need is saved under custom properties, this come out as a large chunk of text that I then apply delimiters and pivots to in order to strip out into the consistent columns of data. All this works perfectly every day :-)

    The issue comes when handling columns with dates. These columns usually contain dates (dd/mm/yyyy) but they also contain some text responses which will vary depending on the circumstances.

    As it stands, I currently output the column as text (shown on the attachment as Current output) which preserves the whole dataset (both dates and text responses), but this means that the date dropdown filter on the output doesn't group dates by month/year etc. This inability to quickly and reliably filter by date is now causing a lot of problems.

    I could simply exclude any non date values (shown on the attachment as Alternative output) but I lose sight of the various text reponses.

    Alternatively I could use a simple If statement in the workbook (shown on the attachment as Alternative formula driven output). This however causes problems because the number of rows changes every day, some of the reporting is done as connection only so doesn't utilise any workbook, and now my data contains formulas rather than values which may cause problems further down the line. I really want to keep this all within PQ because it is so much more reliable and stable, especially when I need to make changes in the future.

    I understand that a text value is a date column is a contradiction in terms, but does anyone know of a way I can run this entirely through PQ??
    Attached Files Attached Files
    Last edited by AliGW; 02-15-2022 at 08:44 AM.

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Power Query error handling in date column

    edit: the below is, in fact, a reiteration of Rorya's earlier suggestion but it returns what you want -- either the date (as a date) or the text string (if not a date)

    = Table.TransformColumns(#"Changed Type", {{"Due date", each try Date.FromText(Text.AfterDelimiter(_, "TEST")) otherwise Text.AfterDelimiter(_, "TEST"), type any}})
    Last edited by XLent; 02-15-2022 at 09:52 AM.

  7. #7
    Registered User
    Join Date
    07-27-2020
    Location
    Manchester, UK
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Power Query error handling in date column

    Arrgh thank you, and apologies for missing the previous post with a solution from Rorya.

    I've applied this to my reporting and it appears to be working exactly as intended. Thank you both for the help!

  8. #8
    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: Power Query error handling in date column

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    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

+ 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] Power Query transformation of unrecognizable date/time format in column with various input
    By Marbleking in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-20-2021, 02:15 PM
  2. [SOLVED] Power Query Unpivot Workbook Tables with Date Column Headings
    By MarvinP in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-16-2021, 10:52 AM
  3. Power Query conditional column on date
    By aldenes in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-28-2020, 11:38 PM
  4. 24 time conversion on custom column under Power Query - getting error
    By cph020283 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-22-2020, 02:20 AM
  5. VBA Error handling for "Send a frown" [Power Pivot/Power BI]
    By Kayees in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-11-2018, 03:25 PM
  6. Replies: 4
    Last Post: 04-26-2018, 11:35 AM
  7. [SOLVED] Insert a date value as a Custom Column in Power Query
    By heytherejem in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-28-2018, 12:42 PM

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