Forum Discussion
How to get a calculated TIMEKEY Column HHMM on Table DirectQuery Mode
I hope this should super simple but I'm new to PowerBI and feeling clueless.
I am accessing Data From a View in DirectQuery where there is a DateTime Column 'StartTime' and I need to be able to add relationship with this to my TimeDimension having TimeKey field in HHMM (Integer).
Therefore I tried creating a calculated column from my StartTime column.
But I just couldn't figure out how to do so. Format funtion doesn't work. Not sure what else actually works. I guess what I need would be somewhat like,
Right("0" + Time.Hour(StartTime), 2) + Right("0" + Time.Minute(StartTime), 2)
I also tried
= Table.AddColumn(dbo_vw_log, "StartTimeKey", each Time.ToText([StartTime], "HHmm"), type text)
~ Here also DirectQuery not supporting above
But then RIGHT also doesn't work. Please can someone help?
Finally Managed to figure out something.. If at all it helps for anyone..
=Table.AddColumn(dbo_VW_LOG, "START_TIMEKEY", each Text.End("00" & Text.From(Time.Hour([STARTTIME])), 2) & Text.End("00" & Text.From(Time.Minute([STARTTIME])), 2))
4 Replies
Thanks for your suggestion. But no luck with Text.End as well. It didn't work
You can try it as a DAX column with
HHMM = FORMAT(DateTime[StartDate], "HHMM")Pat
Finally Managed to figure out something.. If at all it helps for anyone..
=Table.AddColumn(dbo_VW_LOG, "START_TIMEKEY", each Text.End("00" & Text.From(Time.Hour([STARTTIME])), 2) & Text.End("00" & Text.From(Time.Minute([STARTTIME])), 2))
