Forum Discussion
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 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.
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
15 Replies
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])-1Best regards,
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.
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,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.
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,
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.
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,
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.
