CTE's to Direct Query Method in Power Query

[Analystdata]

Hello Everyone,

I am currently working on developing a Bill of Materials (BOM) report in Power BI. Given the substantial data volume, we need to leverage a parameter in Power Query using the Direct M Query method.

We initially attempted to implement the parent-child hierarchy using Common Table Expressions (CTEs) in SQL. However, we've encountered an issue: Power BI does not support loading data from CTE scripts directly through the Advanced options.

Could you please suggest alternative approaches or solutions to handle this situation effectively? Your insights and guidance would be greatly appreciated.

Thank you in advance for your support.

[Greg_Deckler · Accepted solution]

@Analystdata So typically you would rewrite things into Views but the specific solution depends on how you are using the CTE. For example, in this example the CTE was simply moved to the FROM clause:

sql - Re-writing Common Table Expression (CTE) to Subquery to make a compatable PowerBI DirectQuery ...

Solved: Direct Query - Queries using CTE's are not being a... - Microsoft Fabric Community