Forum Discussion

tez's avatar
tez
Resolver I
6 years ago
Solved

Strange error with Date.DayOfWeek function

If I add a column with this function:   = Table.AddColumn(dbo_CMDB_PerformanceData, "WochentagStundeSort", each Date.DayOfWeek([Date], Day.Monday))   the column is displayed correctly in Power ...
Need Help
  • tez's avatar
    tez
    6 years ago

    Hi v-lid-msft ,

    good news: My colleague changed the type from datetime to datetime2 in the MSSQL view. After this both operations worked as expected!

    Regards,

    Thomas

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi tez ,

 

Could you please try to change the Day.Monday to 2? if it does not work, please try to use the following workaround.

 

if Date.DayOfWeek([Date])-1=0 then 7 else Date.DayOfWeek([Date])-1

 

 Best regards,

 

tez's avatar
tez
Resolver I
6 years ago

Hi v-lid-msft 

changing from Day.Monday to 2 does not avoid the error. But the workaround with the if-statement does the job. Do you have an idea, why my statement throws an error, but not the workaround?

In addition there is a second statement, which seems to make a similar strange error:

 

= Table.AddColumn(#"WochentagStundeSort erstellen", "Wochentag-Stunde", each DateTime.ToText([Date], "ddd HH\h"))

 

The format string does not change the error, even if I omit the format string completely. Only removing the line avoids the error.

Do you have a solution for this too?

I´m working in EU area with local setting, can there be a problem with this date locale in some situations?

Regards,

Thomas.