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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Nusc
Helper II
Helper II

Convert 24 HR time

Hi,

 

I have a column of time data in 24 Hr time:

 

Time

625

844

1241

1556

 

 

Where 625 is 06:25 AM.

 

How can I convert this into a usable form for PowerBI?

 

Thank you,

 

 

1 ACCEPTED SOLUTION

The first formula I supplied is DAX. That is the one that starts with "Column =". You use that version after you have run your query and are out of the Query Editor window and in Power BI Desktop. You go to the Data tab (middle icon on the left) and go to "Modeling" and then "New Column". That is where you would use the FIRST formula.

 

The SECOND formula is when you are in the Query Editor window and go to "Add Column" in the ribbon and then New Column. You seem to be using the DAX query when you should be using the M query and using the M query when you should be using the DAX query.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

14 REPLIES 14
diego_salinas
New Member

Maybe you want to use something shorter:

 

Col = REPLACE([Hora],IF(LEN([Hora])=3,2,3),0,":")

 

 

Smiley Wink

 

diego_salinas
New Member

Maybe you want to use something like this:

 

Col = REPLACE([Hora],IF(LEN([Hora])=3,2,3),0,":")

 

Smiley Wink

Greg_Deckler
Super User
Super User

Column = CONCATENATE(CONCATENATE(LEFT([Time],LEN([Time])-2),":"),RIGHT([Time],2))


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

I received the following message: Expression error: The name 'CONCATENATE' wasn't recognized. Make sure it's spelled correctly.

M code version:

 

= Text.Middle(Text.From([Time]),0,Text.Length(Text.From([Time])) - 2) & ":" & Text.Middle(Text.From([Time]),Text.Length(Text.From([Time]))-2,2)

 

This assumes that your original data is a Number, hence the Text.From functions. If you have converted it to Text, you don't need those although the formula as presented will work with either Text or Number as the original data type for [Time] column. 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

I received the following error:

 

The syntax for '.' is incorrect. (DAX(Text.Middle(Text.From([ACTUAL_TIME]),0,Text.Length(Text.From([ACTUAL_TIME])) - 2) & ":" & Text.Middle(Text.From([ACTUAL_TIME]),Text.Length(Text.From([ACTUAL_TIME]))-2,2))).

 

 

error.JPG

Is it still M or DAX?

The first formula I supplied is DAX. That is the one that starts with "Column =". You use that version after you have run your query and are out of the Query Editor window and in Power BI Desktop. You go to the Data tab (middle icon on the left) and go to "Modeling" and then "New Column". That is where you would use the FIRST formula.

 

The SECOND formula is when you are in the Query Editor window and go to "Add Column" in the ribbon and then New Column. You seem to be using the DAX query when you should be using the M query and using the M query when you should be using the DAX query.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

For the first I receive the following error:

 

An argument of function 'LEFT' has the wrong data type or has an invalid value.

 

 

What's the difference beteen DAX and M?

 

 

What is the data type of your "Time" column? Is your Time column called "Time"? I tested with the Time column being Whole Number and Text. 

 

Time.png

 

The difference between DAX and M are that they are two completely different languages. DAX is used with PowerPivot (the in-memory data model that Power BI uses). M is used with Power Query, the extract, transform and load (ETL) system that Power BI uses.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Whole Number for both.

 

 

help1.JPGhelp2.JPG

Try switch [ACTUAL_TIME] to Text, it is the ?Data Type" drop down. Weird. If that doesn't work, copy and paste your formula into a response so that I can copy and paste it exactly and recreate this.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

I'll worry about it later. You're second query worked so I'll just work off that. Thank you!

That would be because you were trying to enter a DAX formula into the Query Editor, which uses "M". Import your data and then in the Data tab, go to Modeling in the ribbon and "New Column". I'll see if I can post the equivalent M formula, but please note that you are posting to a forum on DAX Tips and Tricks, not Power Query "M" Tips and Tricks.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.