Forum Discussion
Resolver IStrange error with Date.DayOfWeek function
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
Community SupportHi 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,
Resolver IHi 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,