Can You Use CTE in Power BI?

Wondering if you can use a Common Table Expression (CTE) in your Power BI reports? The short answer is yes, but not in the way you might expect. This guide will show you exactly how to leverage the power of CTEs to build cleaner, more efficient queries for your Power BI dashboards.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First, What Is a CTE (and Why Should You Care)?

Before we jump into Power BI, let's quickly demystify what a CTE is. A Common Table Expression is a temporary, named result set that you can reference within another SQL statement like a SELECT, INSERT, UPDATE, or DELETE. Think of it as creating a temporary mini-table that only exists for the duration of your query.

That sounds a bit abstract, so here’s a simple analogy: imagine you're baking a cake. Instead of dumping all the ingredients into one giant bowl, you might mix the dry ingredients in one bowl and the wet ingredients in another. Each bowl is a temporary, organized step. A CTE is like one of those bowls - it breaks a big, complex query into smaller, more manageable (and readable) logical steps.

You should care because CTEs make your complex SQL queries:

• More Readable: Long, nested queries become nightmares to debug. CTEs allow you to separate and name each logical step.
• Easier to Maintain: If you need to make a change, you can modify a single CTE instead of digging through a tangled mess of subqueries.
• Recursive: This is the superpower of CTEs. They can reference themselves to work through hierarchical data, like an organizational chart or product categories.

The Nuance: CTEs in Power BI vs. Power Query

Here’s the most important thing to understand: you can't write a CTE natively within the Power Query M formula language. Power Query has its own method for creating logical steps through its user interface and the underlying let ... in expression, which serves a similar purpose of breaking down a transformation process.

However, you absolutely can use CTEs in Power BI by writing a SQL statement directly against your source database. You’re essentially telling Power BI, "Hey, go run this exact SQL query on the server and just give me the final result." This is incredibly powerful because it offloads the heavy data shaping and transformation work to the database server, which is usually much more powerful and optimized for this type of task.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

How to Use a CTE in Power BI: The Step-by-Step Guide

The most common and effective way to use a CTE is when you're connecting to a SQL-based data source, such as SQL Server, PostgreSQL, MySQL, or Azure SQL Database.

Here’s how you do it, using a practical marketing analytics example.

Scenario: You want to identify your top 10% of customers by lifetime value (LTV) from your Sales table and Customers table. This is a multi-step analysis, perfect for a CTE.

Step 1: Get Data from a SQL Server

In the Power BI Desktop home ribbon, click on Get data and select SQL Server database.

Step 2: Open the Advanced Options

In the dialog box that appears, enter your server and database names. Before you click "OK," expand the Advanced options section. This is where the magic happens. You’ll see a box labeled SQL statement.

Step 3: Write Your SQL Query with a CTE

This is where you'll write or paste your query. You don’t want to write a simple SELECT * FROM Sales. Instead, you will provide the complete SQL statement, CTE included, that generates the final table of data you want Power BI to import.

Let's write a query to find those top customers. Our logic will be:

1. Calculate the total revenue for each customer.
2. Rank each customer based on their total revenue.
3. Select only the customers in the top 10%.

Here’s the SQL query using a CTE to accomplish this:

WITH CustomerLTV AS ( -- Step 1: Calculate total revenue for each customer SELECT CustomerID, SUM(OrderAmount) AS TotalRevenue FROM Sales GROUP BY CustomerID ), CustomerRank AS ( -- Step 2: Rank customers by revenue using the CTE above SELECT CustomerID, TotalRevenue, NTILE(10) OVER (ORDER BY TotalRevenue DESC) AS RevenuePercentile FROM CustomerLTV ) -- Step 3: Select only the top percentile from the final CTE SELECT c.CustomerID, c.CustomerName, cr.TotalRevenue, cr.RevenuePercentile FROM CustomerRank cr JOIN Customers c ON cr.CustomerID = c.CustomerID WHERE cr.RevenuePercentile = 1 -- NTILE(10) bucket 1 is the top 10%

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 4: Load the Data into Power BI

Paste this query into the SQL statement box and click OK. Power BI will now connect to your SQL Server, execute the entire query (CTEs and all), and return only the final result set - a clean table of your top 10% customers and their total revenue.

Power BI is now ready to build visualizations from this pre-processed, high-value data. The complexity of ranking and calculating percentiles is all handled by the database before the data even enters your Power BI model.

Why Bother Using CTEs This Way?

Passing a full SQL query with a CTE has some significant advantages over doing all the transformations inside Power Query, especially with large datasets.

1. Enhanced Performance (Query Folding)

This technique is a clear, explicit form of query folding. Query folding is the process where Power Query translates your transformation steps (like filters, joins, and groupings) back into the native language of the data source (like SQL). By writing the SQL yourself, you are taking full control and ensuring the heavy lifting is performed by the database server, not your local machine consuming your own CPU and memory. For large fact tables with millions of rows, this can be the difference between a report that refreshes in seconds versus one that takes minutes or simply times out.

2. Improved Readability and Maintainability

As we saw above, the CTE tidies up our LTV calculation. If you tried to create this same logic with multiple nested subqueries, it would become a developer's nightmare. And while you could achieve a similar result using multiple steps in Power Query, sometimes it's faster and clearer to define complex business logic directly in SQL, especially if you or your team is already proficient in it.

3. Access to Advanced SQL Functions

Databases have powerful window functions (like NTILE, RANK, LEAD, LAG) that can be complex to replicate in Power Query or DAX. SQL provides the most direct and efficient way to perform these calculations at the source. Recursive queries, used to analyze hierarchical data like an employee management structure or a website's navigation path, are another great example where a recursive CTE is the right tool for the job.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Power Query Alternatives to CTEs

It's important to note that you don't always need to use SQL and CTEs. Power Query's built-in functionality is designed to handle step-by-step logic beautifully and visually.

Inside Power Query's Advanced Editor, every transformation you apply through the user interface creates a named step in an M language script structured with a let ... in block. Each step can reference the result of a previous step.

For example, you could mimic the logic of our first CTE like this:

let // Connect to the raw sales data Source = Sql.Database("your_server", "your_database"), dbo_Sales = Source{[Schema="dbo",Item="Sales"]}[Data],

// Step 1 equivalent: Group data to calculate total revenue per customer
GroupedRows = Table.Group(dbo_Sales, {"CustomerID"}, {{"TotalRevenue", each List.Sum([OrderAmount]), type number}}),

// Subsequent steps would continue to transform the 'GroupedRows' table
SortedRows = Table.Sort(GroupedRows,{{"TotalRevenue", Order.Descending}}),
FinalResult = ... // Further steps to rank and filter

in FinalResult

This M code effectively creates "temporary tables" with each step (GroupedRows, SortedRows), much like a CTE does. For many business analysts, this visual, step-by-step approach is more intuitive than writing SQL. The best approach depends on where the data lives, how much of it there is, and what your team's skillset is.

Final Thoughts

So yes, you absolutely can use CTEs in Power BI - just not inside Power Query's M language. By embedding a SQL statement with CTEs directly into your data source connection, you gain a powerful way to organize complex queries, improve performance with query folding, and leverage advanced database functions for more sophisticated analysis.

While handling complex data transformations can feel like jumping through hoops, our goal with Graphed is to eliminate this friction entirely. Instead of deciding between writing SQL or building steps in Power Query, we let you connect your data sources and simply ask questions in plain English. Want to see your top customers by LTV? Just ask, "Show me a chart of our top 10% of customers by lifetime value," and Graphed builds the visualization for you by handling all the complex data work in the background.