cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Automatically convert column data

Hi,

Thanks for the help! I feel like this should be easy but I can't find the DAX.

I have two columns (text) which display time like 12.25 and 13.50. I want to convert these automatically (as data comes in) so it display's in quarters so 12.15 and 13.30 so I can do time lost calculations. Ie. 13.30-12.15 = 1.15, so it's one hour and 15 lost.

How do I convert these numbers?

Thanks for the help!

1 ACCEPTED SOLUTION
Super User

Hey @mwen90 ,

as you said the time is text. So you can split it up and compare.

Try the following calculated column:

``````Time Bucket =
VAR vHours = LEFT( myTable[TimeColumn], 2 )
VAR vMinutes = RIGHT( myTable[TimeColumn], 2 )
RETURN
SWITCH(
TRUE(),
vMinutes > 0 && vMinutes <= 15,
vMinutes & ":" & 15,
vMinutes > 15 && vMinutes <= 30,
vMinutes & ":" & 30,
vMinutes > 30 && vMinutes <= 45,
vMinutes & ":" & 45,
vHours + 1 & ":" & 00
)``````

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

2 REPLIES 2
Community Support

Hi @mwen90

First, you need to change the two columns from integer format to time format .

Original data :

Create calculated column :

time1 = FORMAT(TIME(TRUNC(Sheet1[Column1],0),(Sheet1[Column1]-TRUNC(Sheet1[Column1],0))*60,0),"")

time2 = FORMAT(TIME(TRUNC(Sheet1[Column2],0),(Sheet1[Column2]-TRUNC(Sheet1[Column2],0))*60,0),"")

The effect is as shown:

Then calculated the diff between two time , and change the format .

diff time = FORMAT(Sheet1[time2]-Sheet1[time1],"hh:mm:ss")

The final result is as shown:

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

Hey @mwen90 ,

as you said the time is text. So you can split it up and compare.

Try the following calculated column:

``````Time Bucket =
VAR vHours = LEFT( myTable[TimeColumn], 2 )
VAR vMinutes = RIGHT( myTable[TimeColumn], 2 )
RETURN
SWITCH(
TRUE(),
vMinutes > 0 && vMinutes <= 15,
vMinutes & ":" & 15,
vMinutes > 15 && vMinutes <= 30,
vMinutes & ":" & 30,
vMinutes > 30 && vMinutes <= 45,
vMinutes & ":" & 45,
vHours + 1 & ":" & 00
)``````

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors