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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.