cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
giorgilomidze
Resolver I
Resolver I

Dax time format DD:MM:YYYY HI:MM date time 24 H

i have date time am pm column and i need to convert it in query editor in date time 24 h format: "DD:MM:YYY HH:MM"

How can i write format?


= Table.TransformColumnTypes(dbo_PrxGetWaterLevelData,{{"DATETIME", type datetimezone}})

or in "advanced editor"

 

 

Capture24 hhmm.PNGCapture3423.PNG

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

Hi giorgilomidze,

 

To achieve your requirement, please follow steps below:

 

1.Make sure that the data type of time column has been changed to Data/Time. In Query Editor, click Transform->Data Type->Select Data/Time

1.PNG

2.Then add a new column in which datatime will be transformed to 24h format. Click Add Column->Custom Column, rename the new column and input M code as below:

Time_New = DateTime.ToText([Time],"dd-MM-yyyy HH:mm:ss")

2.PNG

Or you can also add M code as below in Advanced Editor:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Time_New", each DateTime.ToText([Time],"dd-MM-yyyy HH:mm:ss"))

 3.PNG

The result is as below and you can refer to PBIX file: https://www.dropbox.com/s/ytnw7d1cpg8fwp7/For%20giorgilomidze.pbix?dl=0

4.PNG 

 

Best Regards,

Jimmy Tao

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

Hi giorgilomidze,

 

To achieve your requirement, please follow steps below:

 

1.Make sure that the data type of time column has been changed to Data/Time. In Query Editor, click Transform->Data Type->Select Data/Time

1.PNG

2.Then add a new column in which datatime will be transformed to 24h format. Click Add Column->Custom Column, rename the new column and input M code as below:

Time_New = DateTime.ToText([Time],"dd-MM-yyyy HH:mm:ss")

2.PNG

Or you can also add M code as below in Advanced Editor:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Time_New", each DateTime.ToText([Time],"dd-MM-yyyy HH:mm:ss"))

 3.PNG

The result is as below and you can refer to PBIX file: https://www.dropbox.com/s/ytnw7d1cpg8fwp7/For%20giorgilomidze.pbix?dl=0

4.PNG 

 

Best Regards,

Jimmy Tao

can I change format to 24H without converting to text?

yes you can, have a look at the example below.

 

TimeMeasure = FORMAT(CALCULATE (

    MIN('Date'[DateTime]),

    FILTER (

        'Date',

        'Date'[DateTime] = whatever

    )

),"HH:mm AM/PM")

 

its the format that does this, wrap your DAX funtion with a FORMAT and to get the time out of a date.

 

regards,

Rob.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors