Need help with power query

Hi All,

amitchandak 

I have a date column in one of my source. The source used is a smartsheet which is filled manually. The date column in this smartsheet is populated in 2 different formats 1/10/2022 which is read correctly by Power BI as January 10,2022 but if the value is 05-04-22 it is read by Power BI as April 22,2005 whereas it should be May 4 ,2022. 

I have described below what I am thinking to do, but would appreciate any ideas to format the date field consistently.

In order to format the date correctly, I was thinking of using power query and add columns to extract first 2 characters, text between delimiters and the last characters. How can I write an if statement in power query which does something like this :

If delimiter is '-' then extract between 1st and 2nd delimiter else if delimiter is '/' then extract between 1st and 2nd delimiter.

Table.AddColumn(#"Inserted First Characters", "Text Between Delimiters", each if Text.Contains([Date], "-") then each Text.BetweenDelimiters([Date], "-", "-"), type text) else if Text.Contains([Date], "/") each Text.BetweenDelimiters([Date], "/", "/"), type text)