How to convert a Date column consists of US and UK format into standard UK format in power query

[aiyosap]

Hi BI community,

I have a situation in which in my csv file consists of a date column and this date column has two formats (ie., US and UK format). And I would like to transform this date column into one standard UK date format (DD/MM/YYYY) in power query. May I know how to do this?

There is way to do that in excel function but i do not have clue in power query in power BI.

Please help.

Thanks

[amitchandak]

@aiyosap , When we get the data, Power bi expects the date to be the same format as the system date time. Post that is just a display format. Better not to change of give format that, as it can take browser setting and change the format

DD/MM/YYYY to MM/DD/YYYY

Power Query

#date(( Text.End(DD__MM__YY[date],4)), (Text,Middle(DD__MM__YY[date],3,2)) ,(Text.Start(DD__MM__YY[date],2)))

DAX
date(( right(DD__MM__YY[date],4)), (mid(DD__MM__YY[date],4,2)) ,(left(DD__MM__YY[date],2)))

MM/DD/YYYY to DD/MM/YYYY

DAX
date(( right(DD__MM__YY[date],4)), (left(DD__MM__YY[date],2)),(mid(DD__MM__YY[Version_Id],4,2)) )