Forum Discussion
Resolver IReceiving 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 bigintcollectioninsert 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.
Super UserHi EZimmet
The issue here is that Power BI does not allow the use of DECLARE, INSERT INTO, or temporary table-like constructs in DirectQuery mode. Power BI expects a single query to return the dataset without any intermediate steps or variables.
Here’s how you can adjust the SQL query to work in Power BI:
Solution:
You need to rewrite the query to avoid the use of DECLARE and INSERT INTO. Instead, use a Common Table Expression (CTE) or a subquery to achieve the same result.
Here’s a rewritten query using a CTE:
sql
Copy code
WITH SiteIDs AS (
SELECT 21 AS SiteID
UNION ALL
SELECT 5 AS SiteID
)
SELECT
(SELECT SiteID FROM [NZ.Node.Nodes] WHERE ID = P.NodeID) AS SiteID,
P.NodeID,
P.[Date],
P.MAX_PWR,
P.MIN_PWR,
P.AVG_PWR
FROM [NZ.DW.Node.CapIntel] P
WHERE P.NodeID IN (SELECT ID FROM [NZ.Node.Nodes])
AND SiteID IN (SELECT SiteID FROM SiteIDs)
ORDER BY P.[Date], P.NodeID;
Key Adjustments:
Replaced DECLARE and INSERT INTO:
Used a CTE (WITH SiteIDs AS (...)) to define the list of SiteID values.
Kept the main query intact:
Referenced the SiteIDs CTE instead of using a variable or temporary table.
Removed unsupported syntax:
No DECLARE, INSERT INTO, or other procedural statements, which are unsupported in Power BI DirectQuery.
Steps to Implement:
Copy and paste the rewritten query into the Power BI query editor.
Test the connection and validate the data preview.
Apply the changes and the error should be resolved.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Resolver ITY for the reply - I tried the new code and received the following error