Receiving an error when doing a DirectQuery

Hi EZimmet  
Thanks for Posting your query with Microsoft forum Community. 
 

As per your query Receiving an error when doing a Direct Query. In Power BI's Direct Query mode, SQL commands such as DECLARE and INSERT INTO are not supported because Direct Query is designed to pass queries directly to the underlying database. It retrieves data dynamically, and its focus is on executing read-only SELECT statements to pull data into Power BI for visualization. 
 
I've taken a sample dataset and performed the code you have given in direct Query and got the same syntax error. 
 

 
 Based on the code you provided, it functions as expected when executed in IMPORT MODE. The query successfully inserts values into the @SiteIDs collection and retrieves the desired data from the [NZ.DW.Node.CapIntel] and [NZ.Node.Nodes] tables, returning the results ordered by Date and NodeID.
 
DECLARE @SiteIDs bigintcollection 

insert into @SiteIDs 

values (21),(5) 

select (select siteID from [NZ.Node.Nodes] where ID=p.NodeID) AS SiteID,P.NodeID,P.[Date],MAX_PWR,MIN_PWR,AVG_PWR 

from [NZ.DW.Node.CapIntel] P 

where NodeID in (Select ID from [NZ.Node.Nodes] 

WHERE SiteID in (select ID from @SiteIDs)) 

order by P.[Date], P.NodeID 

Please refer to the screenshot below as it worked in IMPORT MODE as expected by using the sample data. 

If this post has helped you, accept it as the correct solution so other members can find it quickly. 

Hope this helps! 

Thanks.