Cannot save a query for live connection

[AverageAsker]

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.

[ChristianBroe]

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

[ChristianBroe]

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

[pqian]

@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)

[AverageAsker]

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.

[konstantinos]

@AverageAsker @pqian  Although I never tried it yet the latest powerBI desktop supports calculated columns

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-latest-update/

[AverageAsker]

As for the latest Power BI Desktop:

that link says that the following

DirectQuery – support for creating Calculated Columns

is supported in the version February 2016 Update (2.32.4307.362)

But I have a newer version and the problem still exists there so the release notes seem to be inaccurate.

[pqian]

@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)

[AverageAsker]

If a calculation in PowerQuery involes Custom Column, then, it doeesn't work as explained above. If you didn't mean Custom Column - can you provide more specifics?

I ended up solving my problem by making all the transformations in SQL.