what else {ts '1899-12-28 00:00:00'} end as "C2", used for?

[QIAO]

Power Bi  created SQL as follows:

select * from (select "dt",
"ITBL"."C2",
"ITBL"."C3"
from
(
select "dt",
"C1",
case
when "C1" is not null
then "C1"
else {ts '1899-12-28 00:00:00'}
end as "C2",
case
when "C1" is null
then 0
else 1
end as "C3"
from
(
select "dt",
"dt" as "C1"
from "thrive_mobiledevelopment"."moduleattemptsummary"
) as "ITBL"
group by "dt",
"C1"
) as "ITBL"
order by "ITBL"."C2",
"ITBL"."C3"

It can't run in my env. What does "else {ts '1899-12-28 00:00:00'} " stand for?

[Anonymous]

The answer is going to entirely depend on the database you are working with but if I had to guess it looks like it is trying to set a timestamp to a valid Date/Time whenever it is null.  Most date/time systems start around then.  I'd normally expect it to be December 31st, but again it could be database specific.

[QIAO]

How to bypass this problem through M language(Connector)?

[Anonymous]

Power Query (M) comes after your SQL statement.  You need to have the correct SQL statement first, especially if you need special conditions prior to the data arriving in Power Query.  The problem isn't with Power BI, its your SQL statement not working with your database.  I'd suggest talking with your DBA and getting them to help you write the correct SQL statement.

[QIAO]

This SQL statement is automatically created by Power BI. 

[Anonymous]

Can you describe the process you went through to have Power BI generate this for you?  This doesn't look like the sort of SQL code i'd see under "View Native Query"

[QIAO]

Just choose a column of Date type. Direct Query enabled.

[Anonymous]

@QIAO  Usually you would need to use Get Data, or write a query long before you can try to bring field into your report.  You have stated that Power BI has generated SQL for you, but that SQL doesn't look like anything i'd expect to see in the Native SQL code Power BI uses.

Are you able to list the steps you used to from a blank Project in order to arrive at having that complex SQL code?

[QIAO]

Steps:

1) Click "Get Data" and connect to database;

2) Enabled Direct Query;

3) Choose Table in Navigator and click Load button;

4) Choose "dt" column in column list;

Btw, if I set column "dt"(Date Type) to no nulls, it can work.

[Anonymous]

Ok great. Sounds like you found the solution.

[QIAO]

I need a way to transform "{ts '2017-10-18 00:00:00'} " into a date type my database can identify. No Nulls is not a solution. 

[v-jiascu-msft]

Hi @QIAO,

1. What's the data source exactly?

2. What's the details of that error in one of your post?

I would suggest you upgrade the Desktop first. It seems you are using an old edition.

Best Regards,

Dale

[Anonymous]

The best advice I can offer is to make use of the "Edit Queries" section of Power BI have the field import as a String.  Next attempt to clean up the field by removing the extra characters such as the { and } brackets and the "ts" string.  Once you clean it down a bit, you can attempt a Data Type conversion from String to DateTime.