Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

Can You Use Temp Tables in Power BI?

Cody Schneider••8 min read

If you come from a SQL background, you're likely familiar and comfortable with using temporary tables to break down complex queries. So, when you move to Power BI, it's natural to ask: can you use temp tables here too? The short answer is no, not in the same way you'd write CREATE TABLE #Temp in SQL. But the great news is you can achieve the exact same outcome - creating intermediate, virtual tables to simplify your logic - using tools built right into Power BI.

GraphedGraphed

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.

Watch Graphed demo video

This tutorial will show you the two primary methods for recreating the functionality of temp tables in both Power Query and DAX. We'll cover how to stage data during transformation and how to create virtual tables on the fly for complex calculations.

What's the Goal of a Temp Table Anyway?

Before diving into the Power BI methods, let's quickly recap why temp tables are so useful in the first place. Whether in SQL or Power BI, the core reasons for using them are the same:

  • Simplifying Complexity: Big, monolithic queries with dozens of nested joins and subqueries are hard to write, read, and debug. Temp tables let you break the process into logical, sequential steps. You solve one piece of the puzzle, store it in a temp table, then work on the next piece.
  • Staging Intermediate Data: Sometimes you need to massage your data into a specific format before performing your final analysis. For example, you might need to unpivot columns, calculate a running total, or aggregate data by multiple groups. A temporary table holds this intermediate result.
  • Improving Performance: In some database systems, materializing an intermediate result into a temp table can be faster than recalculating it repeatedly in a complex query. It allows the database engine to more efficiently process the final steps.

In Power BI, these goals are still incredibly relevant. You will absolutely need to stage intermediate data and simplify complex logic. You'll just use Power BI's own unique tools to do it.

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.

The Power Query Method: Creating "Staging" Queries

Power Query (the "Transform Data" window in Power BI) is where you clean, shape, and prepare your data before it gets loaded into your report's data model. This is the perfect place to replicate the "ETL" (Extract, Transform, Load) function of a traditional temp table.

The trick is to create queries that hold your intermediate steps and then tell Power BI not to load them into the final model. This keeps your model clean and lightweight while still letting you reference those steps in other queries.

Step-by-Step Guide to Power Query Temp Tables

Let's use a relatable example. Imagine you have a large Sales table and you want to create a summary table that only shows sales data for your top 100 customers in North America.

1. Create Your Base Query

First, ensure you have your main data source connected. For this example, let's assume we have a query named All Sales Data that pulls in sales from every region and all customers.

2. Create Your "Temp" Staging Query

Now, instead of modifying the main query, you'll create an intermediate one to act as your temp table.

  • In the Power Query Editor, right-click your All Sales Data query and select Reference. Using "Reference" instead of "Duplicate" is often better because any changes to the source query will automatically flow down to this staging query.
  • Rename this new query to something descriptive, like NA Sales Staging.
  • In this NA Sales Staging query, apply your intermediate transformations. For example, filter the Region column to only countries like "USA", "Canada", and "Mexico". You can perform any number of transformational steps here – pivoting, grouping, merging, etc.

3. The Most Important Step: Disable Load

This is the key to making your query "temporary." You want to use the result of NA Sales Staging in another step, but you don't want this half-finished table loaded into your Data Model when you click "Close & Apply".

  • In the Queries pane on the left, right-click your NA Sales Staging query and uncheck the box for Enable load.
  • The name of the table will become italicized, signifying that it will only exist within Power Query for preparation purposes.

4. Reference your Staging Query to Create the Final Output

Now it's time to perform the final step of your summary calculation:

  • Right-click the NA Sales Staging query and select Reference. This creates one last query. Rename it to something like Top NA Sales Summary.
  • In this new query, perform your final aggregation steps. For example, group by customer and sort descending by total sales to find the top 100 customers. Then use the Keep Top Rows function to only keep the first 100 rows.
  • This Top NA Sales Summary query is the only one with enabled load, so it's the only table that gets loaded into the final data model.

What we've done here is create a chain of queries: All Sales Data (source) → NA Sales Staging (first set of transformations, no load) → Top NA Sales Summary (final transformations loaded into model)

This process is incredibly powerful for breaking down complex ETL and making your workflows significantly easier to manage.

GraphedGraphed

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.

Watch Graphed demo video

The DAX Method: Using Virtual Tables for Measures

While Power Query is great for preparing data at the query level, what if you need to create tables on the fly with calculations? This is where DAX (Data Analysis Expressions) comes in.

In DAX, you don't create physical, stored temp tables. Instead, you use variables and table functions to create virtual tables that only exist in memory for the duration of a single measure calculation. Think of them as scratchpads for complex calculations:

Using Variables to Store a Virtual Table

The core concepts for this method are the VAR keyword, which stores the result of an expression as a variable, and the RETURN keyword, which defines the final output of a DAX measure. You can store an entire table in a variable.

Let's say you want to calculate total sales only for products that have at least $10,000 in revenue. You could do this in one complex calculated formula, or you could break it down:

Total Sales For High Revenue Products =
VAR ProductsWithSignificantSales =
    FILTER (
        VALUES ( Product[Product Name] ),
        SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Unit Price] ) >= 10000
    )
RETURN
    SUMX (
        FILTER ( Sales, Sales[Product Name] IN ProductsWithSignificantSales ),
        Sales[Quantity] * Sales[Unit Price]
    )

Here's a breakdown: we create a virtual table (our temp table, ProductsWithSignificantSales) for products with significant sales. This table only contains the names of products where total sales reach a threshold greater than or equal to $10,000.

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.

Best Practices: When to Use Which Method

Now that you understand the two core approaches, the question is: when should you use Power Query versus DAX? The answer depends on what you are trying to achieve:

  • Use Power Query Staging Queries When: You are performing complex ETL steps that need to be applied to the entire dataset before any analysis occurs, such as aggregations, merging data from different sources, or significant column shaping (like unpivoting). You want to keep a clean data model by amortizing your transformation logic, but not loading all intermediate steps, this is a great option.
  • Use DAX Variables When: You need to create dynamic, intermediate tables inside a single DAX measure that responds to slicer or filter selections, for example, determining a top list that changes based on the year selected. You want to improve the readability and performance of a complex DAX measure by breaking it down into logical chunks (storing a table in a variable is often more efficient than writing an enormously nested IF statement).

Final Thoughts

While you can't create tables temporarily in Power BI quite the same way you can in SQL, you can absolutely achieve the same outcomes. By leveraging referenced and disabled load queries within Power Query or using natural DAX virtual tables, you break down the most complex problems into manageable, understandable steps.

We know firsthand that wrangling complex data streams and spending hours configuring ETL steps, or writing long DAX measures, can be a significant time drainer. That's why we built a platform designed to help you focus on what the data actually means rather than how to prepare it.

Graphed can connect your data sources and then use plain English to ask for the charts and dashboards you need, providing less complexity in the background and freeing you up to focus on what matters.

Related Articles