Forum Discussion
Cannot save a query for live connection
I am building report based on Live data from my SQL Server's table, I imported a table which contains DateTimeOffset column and then added (in Power BI Desktop) a custom column by using this query:
DateTimeZone.ToLocal([CreatedDate])
Once I clicked OK button it the dialog Add Custom Column I have seen my newly created custom column and its values. Then I clicked File and Save in the menu and saw this message
I clicked Apply and then saw this:
If I remove the call to ToLocal - there is no error. Can anybody explain how do I have Live report which contains Power Query calls? Or, if there are other ways to do that (need to display a converted version of the DateTimeOffset stored in my DB) - please advice.
8 Replies
AverageAsker For DirectQuery connections to work, the PowerQuery transformation needs to be mapped to SQL statements. ToLocal() isn't one of those operators that can be translated.
Maybe you can translate it locally once it's loaded in the Model, create a calculated column and do something like = [DateTimeOffset] - TIME(11,0,0) (for 11 hours of difference)
Hi, I found this and it is explaining why I am seeing the error: You cannot add calculated columns or tables so Calculated Columns is not an option unfortunately.
AverageAsker I haven't tried it, so I can't tell for sure. But you can try adding a calculation in PowerQuery, or cast the type to DateTime (I know in SQL casting to datetime usual translates to local time)
I just had the same issue on a column that I casted to another type. My problem was apparently just that the column then had no name:
Before:
SELECT CAST(Startdate as DATE).....
This solved it:
SELECT CAST(Startdate as DATE) as Startdate
Basically a very stupid error message. Not sure if it applies to your problem but it worked for me
I just had the same issue on a column that I casted to another type. My problem was apparently just that the column then had no name:
Before:
SELECT CAST(Startdate as DATE).....
This solved it:
SELECT CAST(Startdate as DATE) as Startdate
Basically a very stupid error message. Not sure if it applies to your problem but it worked for me
