Hour and WeekNum functions not recognized in Power Pivot Query

[jpl458]

Building power pivot table that is connected to SQL Server 2017.  In Edit Table Properties I have the following query:

Master_Accounts.Account_Type_2
,Transaction_Table.Transaction_Type
,Transaction_Table.Description
,Transaction_Table.Date_of_Transaction
,Transaction_Table.Time_of_Transaction
,Transaction_Table.Location_Id
,Revenue=Transaction_Table.Extension
,Transaction_Table.Sku
,Transaction_Table.ArAmt
,Transaction_Table.Balls
,Year=DatePart("yyyy",Transaction_Table.Date_of_Transaction)
,MonthNo=Month(Transaction_Table.Date_of_Transaction)
,Quarter=DATEPART("q", Transaction_Table.Date_of_Transaction)
,DayofMonthNo=Day(Transaction_Table.Date_of_Transaction)
,Hour=Hour(Transaction_Table.Time_of_Transaction)

,WeekNo=WeekNum(Transaction_Table.Date_of_Transaction)

FROM
Master_Accounts
INNER JOIN Transaction_Table
ON Master_Accounts.Master_ID = Transaction_Table.Account_Number

Everything work fine till I add the last 2 lines, Hour and WeehNum.  I get  a msg that state

"Hour is not a recognized function".  The field in () is a DateTime data type.  I even upgraded to SQL Server 2017 to see if that

would make a difference. It did not.

Hope I posted in right spot

Thanks

[v-jiascu-msft]

Hi @jpl458,

Where did you create this report? Power BI or Excel? 

Where did you "Edit Table Properties"? 

Best Regards,

Dale

[jpl458]

Here is how I did it.  Open a blank EXCEL workbook. Then click Power Pivot - Manage, which open the Power Pivot Window. Select Get External Data - From Other Sources which open the Table Import Wizard. Select Microsoft SQL Server then hit Next. In the new window select the proper server from the dropdown list, enter user name and password and the Database name and hit next.  In the new window select the radio button for Write a query that will specify the data to import and hit Next.  In that space you enter the query, wich was in my original message(except I didn't have Select at the beginning).  As you enter the query you can hit Validate and it tells if the the query is OK.  The SQL I entered validates till I get to Hour=Hour(date/time field).  When I  validate it tells me that Hour is not a recognized function, Same with WeekNum, although theyare listed as functions elsewhere in documentation.  I have tried format with no success, but DatePart works for a couple of the lines as does Day.  I need to have Hour and WeekNum for the level of users that will be using this.  I can get around the problem by having a query that runs in ACCESS and gets all of the data including all of the derived fields, and creates a table that EXCEL can load data from.  But that is not clean and requires 2 applications when I should be able to do it in just EXCEL.  So., the problem is why do Hour and WeekNum not work as functions, and is there a work around where I don't have to use ACCESS.  BTW the date and time field are dateTime data types

Thanks