Dealing with various date formats in Power Query

Question

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?

Answer 1

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--