format dd/mm/yy hh:mm:ss timestamp

[p_tmb]

hi, I'm working with a database containing dates in the format dd/mm/yy hh:mm:ss. I'd like to roll up these data to show months, weeks, and days. When I use the field though, powerbi perceives it as a discrete field. what should I do to format it as date and be able to establish a hyerarchy and drill up and down? 

[v-yiruan-msft]

Hi @p_tmb ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Split the column "Data" by delimiter space in Power Query Editor

2. Change the data type of column as "Date/Time" 

Best Regards

[amitchandak]

@p_tmb , as the data is in Aus/Ind/UK format, you need to change the system setting to get it correct

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

You can convert to date like

DD/MM/YYYY to MM/DD/YYYY
date(year( mid(DD__MM__YY[date]7,,4)), month(mid(DD__MM__YY[date],4,2)) ,day(left(DD__MM__YY[date],2)))

check the power query option

Power Query - Text.Start, Text.Middle, Text.End: https://www.youtube.com/watch?v=vky4wPqm0O0