Recognise Column as a Time?

CalvinL ,

Could you check the file ?

Hi camargos88 

Thanks for that. That file seemed to work for me too.. appreciate you answering all of my questions! I've marked your original file post as the solution.

camargos88 

Thank you for the file. So that custom column works, but only goes as far as 04:30:00 for some reason and then just repeats from there. Any ideas as to why it does that?

Also, would there be a solution to just increment the time in the date column rather than creating a new separate column for this?

Hi CalvinL ,

The formula to transform get the last character and transform it to hour like

Hour 4 = The formula gets the last character and multiply it by 30.

Duration.ToText(#duration(0,0, Number.FromText(Text.End([Attribute], 1)) * 30, 0))

The last value is Hour 9, so it is 04:30 starting from 00:00.

If you need the Hour 1 to be 00:00 the code is like

Duration.ToText(#duration(0,0, (Number.FromText(Text.End([Attribute], 1)) 1) * 30, 0))

So Hour 9 would be like 4:00.

I didn't get this part:

"Also, would there be a solution to just increment the time in the date column rather than creating a new separate column for this?"

Can you give me some example ?

Thank you camargos88 

So I've got 48 values for half hour.. how would my code look like for that? Half hour 1 would be 00:30, half hour 2 would be 01:00 up to half hour 48 which would be 00:00

Re: "Also, would there be a solution to just increment the time in the date column rather than creating a new separate column for this?"

What i meant by that is - can we use a function like the DateAdd function to just increment the current date column by +00:30?

Hi CalvinL ,

I didn't try it after Hour 9, so the code would be like:

Duration.ToText(#duration(0, 0, 30, 0) * Number.FromText(Text.Split([Attribute], " "){2}))

Also, Hour 48 will be next day ?

I've updated the file.

camargos88 Values are being recorded every half hour (30 mins) hence the 00:30. Sorry for any confusion.