Data loads in Power Query but not Power BI

[andypettit12]

Hello!

Don't really have SQL experience so I don't know where to begin to find the issue here. Data shows up fine in Power Query, no issues in preview:

Also no issues loading into Excel. But when I try to load into Power BI, I get this message:

Here is the query I'm exporting from Wonderware Query:

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20231219 23:26:30.309'
SET @EndDate = '20240320 23:26:30.309'
SET NOCOUNT OFF
SELECT * FROM (
SELECT History.TagName, DateTime, Value, vValue, SourceTag, StartDateTime
FROM History
WHERE History.TagName IN ('P6_M1_Batch_Active_ID')
AND wwRetrievalMode = 'Delta'
AND wwTimeDeadband = 500
AND wwQualityRule = 'Extended'
AND wwVersion = 'Latest'
AND DateTime >= @StartDate
AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate

I'm just pasting this into the SQL Statement area when getting data from the SQL server. This is what Power Query is showing in the advanced editor:

let
    Source = Sql.Database("PLT111", "Runtime", [Query="SET NOCOUNT ON#(lf)DECLARE @StartDate DateTime#(lf)DECLARE @EndDate DateTime#(lf)SET @StartDate = '20231219 23:26:30.309'#(lf)SET @EndDate = '20240320 23:26:30.309'#(lf)SET NOCOUNT OFF#(lf)SELECT  * FROM (#(lf)SELECT History.TagName, DateTime, Value, vValue, SourceTag, StartDateTime#(lf) FROM History#(lf) WHERE History.TagName IN ('P6_M1_Batch_Active_ID')#(lf) AND wwRetrievalMode = 'Delta'#(lf) AND wwTimeDeadband = 500#(lf) AND wwQualityRule = 'Extended'#(lf) AND wwVersion = 'Latest'#(lf) AND DateTime >= @StartDate#(lf) AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate", HierarchicalNavigation=true])
in
    Source

What am I missing here? Any help would be greatly appreciated. Thank you!

[v-jingzhan-msft]

Hi @andypettit12 

You may try this 

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20231219 23:26:30.309'
SET @EndDate = '20240320 23:26:30.309'
SELECT * FROM (
SELECT History.TagName, DateTime, Value, vValue, SourceTag, StartDateTime
FROM History
WHERE History.TagName IN ('P6_M1_Batch_Active_ID')
AND wwRetrievalMode = 'Delta'
AND wwTimeDeadband = 500
AND wwQualityRule = 'Extended'
AND wwVersion = 'Latest'
AND DateTime >= @StartDate
AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate

Based on my test, DECLARE and SET are supported. Probably "SET NOCOUNT ON" and "SET NOCOUNT OFF" is not supported. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Joe_Barry]

Hi @andypettit12 

Maybe Power BI doesn't support that syntax. I would suggest removing the SET statements and using only Select instead.

Thnaks

Joe

If you found my answer helpful and it solved your issue, please accept as solution