Using Recursive CTEs in Power BI

Question

Power BI has some limitations on Direct queries that has proved a hurdle for me to work around; when using direct Query Power BI takes any query that you input and adds a

Select * From(

statement to the beginning of the code, which prevents the running of any stored procedure, declaring variables, or use CTE Expressions to create temporary/derived tables.

I pulled the Stored Procedure Code out of the procedure, but i cannot figure out how to convert the whole statement from a CTE Expression to a derived expression. Does anyone know how to covert the query? i have only had success with the first select statement, but doing so prevents the Union, the Inner Join, and the relationship given between the two select Statements.

Original Procedure

WITH MaterialsTree(PartNo,SubPartNo,Descrip,Qty,Unit,Purchased,Vendor,TotalQty,UnitCost,UnitPrice,TotalCost,TotalPrice,TotalWt,
      StepNo,PartWt,Materials_ID,ItemNo, ParentID) AS 
(
    SELECT PartNo,SubPartNo,Descrip,Qty,Unit,Purchased,Vendor,TotalQty,UnitCost,UnitPrice,TotalCost,TotalPrice,TotalWt,
      StepNo,PartWt,Materials_ID,ItemNo, CAST(NULL AS INT) AS ParentID
        FROM Materials AS Mat
        WHERE PartNo = @PartNo
    UNION ALL
    SELECT m.PartNo,m.SubPartNo,m.Descrip,m.Qty,m.Unit,m.Purchased,m.Vendor,m.TotalQty,m.UnitCost,m.UnitPrice,m.TotalCost,m.TotalPrice,m.TotalWt,
      m.StepNo,m.PartWt,m.Materials_ID,m.ItemNo, t.Materials_ID AS ParentID
        FROM Materials AS m
        INNER JOIN MaterialsTree AS t 
            ON m.PartNo = t.SubPartNo
)
SELECT PartNo,SubPartNo,Descrip,Qty,Unit,Purchased,Vendor,TotalQty,UnitCost,UnitPrice,TotalCost,TotalPrice,TotalWt,
      StepNo,PartWt,Materials_ID,ItemNo, ParentID 
FROM MaterialsTree

You can't convert a recursive CTE into a subquery, what you call a derive table. These are definitely *not* derived tables. In the simplest case there's no difference between subqueries and CTEs except the layout. When it comes to recursion though, a subquery can't refer to itself by name — Panagiotis Kanavos, Oct 04 '22 at 12:47
As for calling that query in Power BI, you can create a view from that query and call the query from Power BI — Panagiotis Kanavos, Oct 04 '22 at 12:50
I have created a View, but i need to be able to specify the Value in the first "where" callout; i will edit the question to show that as a parameter. if it were a single item that needed the query a view would be great, but i cant parameterize the view to have it be adjustable — LastSparton, Oct 04 '22 at 12:54
You can. `SELECT * from dbo.SomeFunction(123)`. I repeat, you can't perform a recursive query without recursive CTEs. — Panagiotis Kanavos, Oct 04 '22 at 12:59
Besides, there's certainly a way to use stored procedures with Power BI — Panagiotis Kanavos, Oct 04 '22 at 13:00
in Power BI you cannot use a stored procedure in Direct Query Mode; the 'official' answer, is to import the data into Power BI. however, that restricts you to only have data up-to-date during refreshed. otherwise you cannot put commands like "EXEC", "WITH" or others inside of a Select Statement. — LastSparton, Oct 04 '22 at 13:05
Power BI's power comes from storing the data and analyzing it itself, not from mapping query results to charts — Panagiotis Kanavos, Oct 04 '22 at 13:31

score 1 · Answer 1 · answered Oct 04 '22 at 13:10

1

I converted the Stored Procedure over to a Function; this was allowed. thank you for the guidance.

answered Oct 04 '22 at 13:10

LastSparton

43
8

Using Recursive CTEs in Power BI

1 Answers1