Strange error with Date.DayOfWeek function

[tez]

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 BI Query Editor. But when I close the Query Editor and update the data, an error is shown:

 If i click on "show error" this is displayed:

 When I delete "Day.Monday" in the query (so Sunday as default is used), everything works without problems! I cannot even use "Day.Sunday", this produced the same error.

The Date field contains only DateTime values, no null or empty values.

This seems very strange to me, does anyone have an idea?

Regards,

Thomas.

[v-lid-msft]

Hi @tez ,

Because the workaround does not use second parameter of function, which seems casue the issue. What is the version of your power bi desktop, have you tried to reproduce with a blank pbix file which contain several rows of fake data?

The ToText function also works on my side, have you tried to use the DAX Format function to get the result (create a calculated column)?

Column = Format([Date],""ddd HH\h")

Update: we have no idea why your last reply comes into spam box, but if you require,  we can restore it.

Best regards,

[tez]

Hi @v-lid-msft ,

I tried the DAX-workaround, it worked. Unfortunately I have to repeat it many times, because the original table is referenced within many other tables.

I also copied the imported table to a Power BI local table, and used the same Power Query functions: They work without problems.

How can it be, that an import from MSSQL throws the strange error, and an exact copy of the same data works without problems?

I even created a new PBIX file from scratch with the same DB connection, again the error appears when using the original function calls mentioned above.

I can send the PBIX file, but I dont know wheter it is of use without the MSSQL DB.

Regards,

Thomas.

[v-lid-msft]

Hi @tez ,

The pbix file cannot help, please do not put it in this thread. We test with the following queries and find the last step does not convert into native query.

let
    Source = Sql.Database("localhost", "pbitest"),
    dbo.TableName = Source{[Schema="dbo",Item="TableName"]}[Data],
    #"Added Custom" = Table.AddColumn(dbo.TableName, "WochentagStundeSort", each Date.DayOfWeek([DateTime], Day.Monday))
in
    #"Added Custom"

when delete the second parameter, it will convert into following native query:

select [_].[id] as [id],
    [_].[DateTime] as [DateTime],
    datepart("dw", [_].[DateTime]) as [WochentagStundeSort]
from [dbo].[TableName] as [_]

If your query is success with the Date.DayOfWeek([DateTime]), it seems the problem is in power query side not MSSQL.  You can also check with the "View Native Query" or "Diagnose with the issue step.

If it is an urgent issue, We suggest you to open a ticket here for help if you are a pro user: https://powerbi.microsoft.com/en-us/support/

Best regards,

[tez]

Hi @v-lid-msft ,

does this mean, this is a bug in Power BI?

Regarding the menu options: "View native query" is greyed out, I cannot klick it. And "Diagnos" can be clicket one, but without result; after this it is greyed out too. Is there some documentation how to use these menu options?

Regards,

Thomas.

[v-lid-msft]

Hi @tez ,

It seems more like a special issue instead of common bug, we cannot reproduce it. Does the Diagnos button in the top work?

Document for refer: https://docs.microsoft.com/en-us/power-query/recordingquerydiagnostics

Best regards,

[tez]

Hi @v-lid-msft,

yes, I managed to do a diagnosis. But to be honest, I cannot get a clue what went wrong from the diagnosis data. Do you want me to post it here?

An additional insight from testing: The same MSSQL table does not produce the error when accessing it directly, without a view. But my colleague who created the view checked it twice and could not find a cause for this.

Regards,

Thomas.

[tez · Accepted solution]

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]

Hi @tez ,

Glad to hear you have solve your issue, sound like a great solution, thank you for sharing, we think it will help more users who have similar problems in forum.

Best regards,

[tez]

@v-lid-msft thank you. I use Power BI Desktop Version: 2.80.5803.1061 64-bit (April 2020). I will try your recommendations today or tomorrow and get back to you. If I use a local copy of the table or an Excel file, the error is NOT reproducable, but I will do further testing.

[v-lid-msft]

Hi @tez ,

I guess the table is in DirectQuery Mode? so when custom column created, it transform into the native query and return error result. We think you may need to check the query received by the source, if table is in Directquery mode.

Best regards,

[tez]

Hi @v-lid-msft ,

no, I´m in import mode.

Regards, Thomas.

[v-lid-msft]

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]

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.

[camargos88]

Hi @tez ,

Can you provide this pbix ? Please mask some sensetive data.

[tez]

@camargos88 

thank you for your reply; I´m afraid you cannot use the file, because the error occurs when refreshing from the MSSQL Server, it does not occur with local data (I tried with a local table and an Excel datasource).

Regards,

Thomas.