Convert 24 HR time

1
votes

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

Time

625

844

1241

1566

Where 625 is 06:25 AM.

How can I convert this into a usable form for PowerBI? I tried the following M query in the advanced editor Column = CONCATENATE(CONCATENATE(LEFT([Time],LEN([Time])-2),":"),RIGHT([Time],2))

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

Thank you,

2
powerbipowerquerym
Updated tags on this thread. – Nusc

2 Answers

1
votes

This can be done easily using DAX by creating a new column in you model and use this expression:

=FORMAT(TimeTable[time];"00:00")

You can set type and format to the column:

enter image description here

This will return something like this:

enter image description here

I hope 1566 time value is a typo in your question.

Note I am using Excel with PowerPivot but this approach can be applied to Power BI too.

Let me know if this helps.

0
votes

M queries are not written like Excel or DAX formulas. Look at this site to learn more about how to write M queries.

In your case, you could add this formula in a custom column (assuming your times are in Column1):

Time.ToText(#time(Number.RoundDown([Column1] / 100), Number.Mod([Column1], 100), 0), "hh:mm tt")

If you want to keep it as a time value, then remove the Time.ToText function in the example above.