Power BI

[Ania]

Hi,

In my model I got column "Date" with two types of dates: "day-month-year" and "month/day/year". What would be the formula to unify the date format?

I know how do do that in SQL (

IF(`date` RLIKE "[0-9]*/[0-9]*/[0-9]*", concat_ws("-", split(`date`, "/")[1], split(`date`, "/")[0] ,split(`date`, "/")[2]), `date`))...any hint how to approach that in P-BI?

[waltheed · Accepted solution]

You can use the DATE function for that, together with MID.

MyDateColumn = date("2018", "4", "17")   this returns a data value.

Fill in the Year, Month and Day by doing a MID function on your string values. 

e.g. MyYear = MID("25/04/2018",7,4)    this returns 2018.