Forum Discussion
Helper IIRecognise Column as a Time?
Community Champion
Helper IIcamargos88 Appreciate the reply. Unfortunately, that didn't work for me. I found another way around it though. I went into my data set and unpivoted the set so that there's a half hour 1, half hour 2 etc. value for each date. Now my problem is changing all the times in the date column to match the half hour 1, 2 etc.
Sample data (what it looks like now):
| Date | Attribute | Value |
| 01/01/2020 00:00:00 | Half hour 1 | 10 |
| 01/01/2020 00:00:00 | Half hour 2 | 12 |
| 01/01/2020 00:00:00 | Half hour 3 | 15 |
| 01/01/2020 00:00:00 | Half hour 4 | 16 |
- camargos88
CalvinL ,
The m code does it.
Check this file: Download PBIX
Did I answer your question? Mark my post as a solution!
Ricardo- CalvinL
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?
- camargos88
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 ?