Dealing with various date formats in Power Query

Sean Morson 1 Reputation point
2022-09-28T16:47:40.027+00:00

I am using Power Query to connect to a number of external data sources (Excel Tables), all of which are appended into one query. The query has a date column for all sources.

I have:

  • some date entries as numerical (44866),
  • some as short date (01/09/2022), and
  • some as date ranges (e.g. 28/09/22 - 30/09/22 - this is always a range in the current month).

My aim is to cleanse all of this to arrive at a list of months, or even short date format will do (01/MM/YY).

My first attempt using 'column from examples' managed to convert the date ranges to match the short dates, but the numerical entries confused it and resulted in errors.

Alternatively, if I first convert the data type to Date, the date range strings are converted to Errors, but I am struggling to replace the errors with the Current Month, using Power Query M.

What is the best way to replace the Errors with a date in the format "01/(current month)/(current year)"

Alternatively, is there a recommended best practise to cleanse this range of data entries?

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,455 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Dave Patrick 426.2K Reputation points MVP
    2022-09-28T17:16:19.833+00:00

    Power query is not currently supported here on Q&A. The product group for Power Query actively monitors questions over at
    https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

    --please don't forget to Accept as answer if the reply is helpful--

    0 comments No comments