cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft
Microsoft

equivilent of Excel HOUR() in PowerBI

I am trying to take just the HOUR out of a date time column in PowerBI.

I tried

=HOUR([DateTime])

, which is converted into

= Table.AddColumn(#"Reordered Columns", "Custom", each HOUR([LocalTime]))

but I get

Expression error: The name 'HOUR' wasn't recognized.  Make sure it's spelled correctly.

I have scoured the internet, anyone have a place I can look for the answer?

1 ACCEPTED SOLUTION
Community Champion
Community Champion

Looks like you're in the query editor which uses a completely different language. The function you're looking for is Time.Hour()





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Community Champion
Community Champion

Looks like you're in the query editor which uses a completely different language. The function you're looking for is Time.Hour()





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Perfect!

 

i went with =time.hour([LocalTime]) and it worked. thanks.

 

So i am dealing with Power Query code?

@zamurr @KHorseman

 

=time.hour([DateTime]) is not working in my case. I don't know what's wrong.

 

I have this scenario:

 

Instead of each StartOfDay to be 12:00:00, the TradingDay begins at 5:00:00 AM of each day. So, my solution is to make a custom column that will determine the correct TradingDate for each transaction based on the given condition (i.e. 5:00:00 AM start of Trading Day). I got it to work in Excel thru HOUR() function but I can't get it to work in powerBI. My Excel formula is shown below.

 

=IF(HOUR(DateTime)>4,TEXT(DATE(YEAR(DateTime),MONTH(DateTime),DAY(DateTime)+1),"mm/dd/yyyy"),TEXT(DATE(YEAR(DateTime),MONTH(DateTime),DAY(DateTime)),"mm/dd/yyyy"))

 

can you help me with this please? 

 

Thanks a lot.

@valQuentineCan you be more specific? What do you mean by "not working"? Is it giving you some sort of error message? Is it giving you an inaccurate result?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you for a prompt reply @KHorseman.

 

I have tried this custom column in powerBI:

 

TradDate = IF(Time.Hour([DateTime])>4,[DateTime]+1,[DateTime]) 

 

And it returned an error "Failed to resolve name Time.Hour. It is not a valid table, variable, or function name."

 

What causes this error?

 

As I said earlier, Time.Hour() is a query formula, not a DAX formula. It's a different language, used only in the query editor. You appear to be trying to write a DAX formula in your table editor.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Community Champion
Community Champion

@zamurr yep that's it exactly.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors