Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
QIAO
Helper II
Helper II

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

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?

12 REPLIES 12
Anonymous
Not applicable

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.

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

Anonymous
Not applicable

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.

 

This SQL statement is automatically created by Power BI. 

Anonymous
Not applicable

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"

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

WeChat Image_20180508122109.png

Anonymous
Not applicable

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

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
Not applicable

Ok great. Sounds like you found the solution.

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. 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.