How to Copy a Table in Power BI

Need to create a copy of a table in Power BI? It's a common task, whether you're testing new measures, creating a subset of data for a specific report, or just want a sandbox to experiment in without messing up your original query. This guide will walk you through three different methods for duplicating tables, each useful for different scenarios.

Why Would You Want to Copy a Table?

Before we jump into the "how," let's quickly cover the "why." Duplicating a table isn't just about creating a backup, it's a powerful technique used for several strategic reasons in data modeling:

Creating a Sandbox Environment: This is the most common reason. You might want to test complex new transformations, add experimental calculated columns, or write some tricky DAX measures. By working on a copied table, you can do all this safely without risking any errors in your primary, production-ready table.
Building Role-Playing Dimensions: In more advanced data models, you might need the same dimension table to play multiple "roles." A classic example is with dates. You might have an 'Order Date' and a 'Ship Date' that both need to be linked to your calendar table. Duplicating the calendar table allows you to create two independent date dimensions, one for each role.
Creating Specific Data Subsets: Sometimes, a report only needs a filtered version of a larger table. For instance, you could copy your main 'Sales' table to create a new USA_Sales table that only contains data for the United States. This can simplify your model and even improve performance for specific reports.
Experimenting with Different Relationships: You might want to see how changing the links between tables affects your report. Duplicating a table gives you the freedom to test new relationships without breaking the existing model.

Method 1: The Quick and Easy Way with "Duplicate" in Power Query

For most situations, the simplest way to copy a table is right inside the Power Query Editor. This method creates an exact, independent copy of your table, including every transformation step you’ve already applied.

Here’s how to do it step-by-step:

On the Home tab of Power BI Desktop, click on Transform data. This will launch the Power Query Editor.
In the Queries pane on the left side of the window, find the table you want to copy.
Right-click on the table’s name. A context menu will appear.
Select Duplicate from the menu.

That’s it! Power BI will instantly create a new query with "(2)" tacked onto the end of the original name (e.g., if you copied 'Sales', the new table will be called 'Sales (2)'). You should immediately rename this new table to something more meaningful by right-clicking it and selecting Rename.

Understanding 'Duplicate' vs. 'Reference'

When you right-click a query, you'll see two options that sound similar: Duplicate and Reference. It's important to know the difference.

Duplicate: This creates a completely independent copy of the query. Every single applied step from the original query is copied over. From this point forward, any changes you make to the new duplicated query will not affect the original, and any changes to the original won't affect the duplicate. Think of it as taking a photocopy, you now have two separate documents.
- Use this when: You want a truly separate, editable copy for testing or to build a slightly different version of an existing table.
Reference: This creates a dependent query that uses the final output of the original query as its starting point (its source). A referenced table has no applied steps to begin with - it simply points to the end result of the source table. If you add or modify steps in the original query, those changes will cascade down and automatically update the referenced table before any of its own steps are applied.
- Use this when: You want to create multiple "branches" from a single, clean base query. For example, you could have a base 'AllSales' query with all the heavy cleaning and transformations. Then, you could create several referenced tables from it - Sales_2023, Sales_2024, Sales_US - each with just one or two filtering steps of its own. This saves you from repeating the same cleaning steps over and over.

Method 2: Creating a Calculated Table with DAX

Another powerful method is to create your table copy using DAX (Data Analysis Expressions). This is done in the main Power BI Desktop window, not in the Power Query Editor. This approach creates a new table in your model based on the output of a DAX formula, giving you a ton of flexibility.

Here’s how you can create a table using DAX:

Go to the Report View or Data View in Power BI Desktop.
Click on the Modeling tab in the top ribbon.
Click the New Table button.
The formula bar will appear at the top. This is where you'll write your DAX expression.

Here are a few common DAX formulas for creating tables:

To make an exact copy of a table:

This simple formula creates a NewSalesTable that is a perfect copy of the AllSalesTable. Note that it copies the data and relationships but does not copy over any of the transformation steps from Power Query.

To create a filtered copy of a table:

Let's say you want a table containing only sales records from your 'Electronics' category.

To create a copy with only specific columns:

If you want a summarized version of a customer table with just a few columns, you can use SELECTCOLUMNS or SUMMARIZECOLUMNS.

Once you enter your formula and press Enter, the new table will appear in your 'Data' pane. A key difference here is that this table is "calculated" - it doesn't have a source in Power Query and is generated when your data model refreshes. This can be more efficient than duplicating a query in Power Query, as Power BI doesn't have to query the original data source twice.

Method 3: Go Advanced by Copying the M Code

For maximum control, or if you need to copy a table's entire query structure to a completely different PBIX file, you can copy its underlying M code from the Advanced Editor.

This method sounds technical, but it’s really just a copy-and-paste job. Here’s the process:

Open the Power Query Editor (from the 'Home' tab, click 'Transform data').
Select the query (table) you want to copy from the 'Queries' pane on the left.
Go to the Home tab and click on Advanced Editor. (Alternatively, you can go to the 'View' tab and find it there).
A new window will pop up showing the M code for all the transformation steps applied to your table. Select all the text (Ctrl + A) and copy it (Ctrl + C). Click Done to close the window.
Now, to create the new table, go to the Home tab in Power Query and select New Source > Blank Query.
A new query will be created called Query1. Select it, and then click Advanced Editor again.
Delete any existing code in the blank query's Advanced Editor and paste the code you copied earlier (Ctrl + V).
Click Done.

You now have an identical query copy! The final, and most important step, is to rename the Query1 in the 'Queries' pane to something descriptive. This method is incredibly useful for migrating complex query logic from one report to another without having to rebuild it step-by-step.

Choosing the Right Method for Your Goal

Not sure which approach is best for you? Here's a quick cheat sheet:

Use Method 1 (Duplicate in Power Query) when you need a simple, independent copy for testing or modification within the same report. It's the fastest and most straightforward option.
Use Method 2 (DAX Calculated Table) when you want to create a dynamically filtered or aggregated summary of an existing table without adding to your Power Query refresh time. It’s perfect for modeling and creating customized tables from data already in your report.
Use Method 3 (Copy M Code) when you need to move a complex set of transformations to another report or want a text-based backup of your query steps.

Final Thoughts

Copying tables in Power BI is a fundamental skill that opens up more advanced data modeling and analysis possibilities. Whether you're using Power Query's simple duplicate feature, a flexible DAX calculation, or copying the M code, you now have the tools needed to manage your data model more effectively and safely experiment with new ideas.

Building dashboards often involves manipulating data just like this, which can become time-consuming with complex tools like Power BI. At Graphed, we want to help you skip right to the insights. That's why we built an AI data analyst that allows you to connect all your data sources - from Google Analytics and Shopify to your CRM - and then use natural language to create dashboards. Instead of worrying about DAX formulas or M code to get a specific view of your data, you can simply ask things like, "Show me my sales from UK customers last quarter," and get your answer instantly. The goal is to spend less time wrangling data and more time acting on it. Find out how Graphed can automate your reporting and give you back valuable time.