Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aiyosap
Helper II
Helper II

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

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?

 

1.JPG

 

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

Please help.

 

Thanks

 

1 REPLY 1
amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.