Power BI allows users to duplicate full tables, copy specific columns or create references in Power BI using Power Query or DAX. While there are many use cases for copying tables in Power BI, The specific use case and end user experience should be considered before creating them. We’ll look at when to use each method and why you would choose one method over another.
Before diving into the different methods for copying tables in Power BI, let’s consider the different use cases that might come up.
Table of Contents
When Should You Create a New Table in Power BI?
The ability to duplicate tables, columns, and create reference tables in Power BI in an essential skill for Power BI developers. Unlike other data preparation tools, Power BI makes it difficult to apply parallel transformations to an intermediate starting step without duplicating the data table. Table duplication enables you to apply different transformations to the same starting data.
Some scenarios where you may want to duplicate a table or create a reference table are:
- Data Exploration – After duplicating a table, users can easily summarize it, filter it, and make changes without impacting the starting data.
- Transformation Testing – Sometimes you won’t know what data prep steps to take until you see them. A duplicate table lets you play around in the equivalent of a data sandbox to test out and try new things without impacting your starting data.
- Query Optimization – We generally avoid creating DAX tables because of the negative impact on user experience. Duplicating tables in Power Query allows developers to generate summary tables that will improve visualization interaction time.
- Simplifying Complex Transformations – Creating a duplicate table or a reference table will allow you to split data prep into multiple smaller pieces. At some point the transformation list becomes so long that it’s difficult to follow all of the steps.
- Dynamic Data Filtering – Rather than writing Power BI DAX to generate and filter tables, tables can be generated, summarized and filtered in Power BI. Then joined or merged with other tables to act as a filter.
There are a number of additional use cases not listed here where creating copy of a table will come in handy. These are a just a few of the Power BI use cases that we find most common for creating duplicate tables.
How to Create a New Table in Power BI Using an Existing Table in Power Query
There are two methods for creating duplicate tables in Power BI. You can either create a duplicate table in Power Query or you can use DAX. To create a duplicate table using Power Query, open the Power Query Editor, Select the Table Name, Right Click, and select Duplicate.
The result will be a new table with a number at the end of the table to identify it as a duplicate. You can double click into the name of the new table to modify it.
Note: Creating a duplicate table will copy all of the Power Query data transformation steps that have already been applied.
How to Create a New Table from an Existing Table Using Power BI DAX Formulas
To create a copy of an existing table in Power BI using a DAX Formula, click on New Table, From the Table Tools Section of the Power BI Ribbon on the Report View screen. After clicking New Table, a formula entry box will appear.
The DAX Formula for creating a New Table is: Duplicate Table = ‘Old Table Name’
In the example blow, we create a copy of an existing table called CookieSales
This will create an exact duplicate of the table that you can work with and assign to different visualizations. A more common scenario for creating a table in DAX is assigning a filter or group of filters to it. By applying filters at the table level, it prevents the developer from having to filter every single measure on the table.
How to Duplicate a Table in DAX and Apply Filters
When creating a duplicate table in DAX you can point a new table to equal an old one. To apply filters to it, you will need to the CALCULATETABLE DAX formula. The Syntax for it is:
Table Name = CALCULATE TABLE (‘Table Name, Filter Criteria)
In practice, it would look like the following.
FilteredCookieSales = CALCULATETABLE( CookieSales, CookieSales[Month] = 7 )
The end result when measures from the new duplicate table are added to a visual are pre-filtered values that look like the screenshot below.
Note: You can apply as many filtering criteria as you want in a DAX Formula for duplicating an existing table. However, there may be a negative impact on end-user performance when filtering and exploring the report dashboard.
How to Choose Between Power Query and DAX to Duplicate a Table
The biggest difference between DAX and the Power Query Editor for duplicating a table in Power BI is the timing that the query is executed. Power Query data transformations are performed when data is refreshed, and the transformed data is saved as part of the .pbix file. DAX measures are calculated as needed, typically when a user refreshes a visual by applying a slicer or drilling down into a lower level of detail.
Power BI Dataset Size Limits
Duplicating data in Power Query will result in a larger dataset size because the full set of starting data is copied. Power BI has file size and dataset size limits based on the licensing tier of the user. Users with Pro licenses are limited to 1 gig of data, which premium users have variable resources available based on tier.
Using Reference Tables Instead of Duplicate Tables in Power BI
An alternative to creating a duplicate of a table in Power BI is creating a Reference Table. Reference Tables create linked versions of existing tables in your data model which you can apply additional transformation steps to.
While a duplicate table creates an independent copy of the data and transformation steps, a reference table starts as a copy of the starting table and links to it.
This means that any changes made to the original table’s data transformations are automatically reflected in the reference table. It also means that you can avoid the duplication of starting data to reduce the size of your data model.
To create a Reference Table in Power BI, launch the Power Query Editor, Right Click a Table Name, and Select Reference.
The new reference table will be generated with a link to the prior table.
The following chart provides a comparison of using a Duplicate Table or Reference Table to help you choose the one that is right for your use case.
| Feature | Duplicate Table | Reference Table |
|---|---|---|
| Data Duplication | Yes, duplicates the data and transformations. | No, refers to the original data and transformations. |
| Independence | Independent from the original table. | Dependent on the original table. |
| Memory Usage | Higher, as it creates a separate copy of the data. | Lower, as it links to the existing data. |
| Transformation Steps | Includes all transformations from the original. | Starts with all transformations but can be modified without affecting the original. |
| Query Efficiency | Less efficient for queries that need a similar data structure. | More efficient as it uses the same data structure. |
| Use Case | When you need a separate table to apply different transformations without affecting the original. | When you need to apply additional transformations on top of an existing table without modifying it. |
| Change Propagation | Changes to the original table do not propagate. | Changes to the original table are reflected in the reference table. |
| Flexibility | High flexibility for adding or removing transformation steps independently. | Limited flexibility as it initially inherits all transformation steps from the original. |
As you can see there a number of nuances between when to duplicate a table, and when to use a reference table. One of the main concerns is the amount of space your data model takes up.
Quick Tip: To view how large your data model is, and to the view the contents of a .pbix file, change the file extension to .zip. This allows you to open a .pbix file and view the contents of it. Change it back from a .zip to a .pbix when you’re ready to re-open it in Power BI.
Using Reference Tables to Simplify Data Transformations
A primary use case for reference tables is to simplify data transformations. If you find that the list of transformations in Power Query is getting excessively long, you can break data prep into smaller steps. Perform a smaller set of steps on a table, then create a reference table and perform the next set of steps.
By splitting data prep in Power Query into smaller pieces, you will be able to more easily get complex datasets to the place they need to be. Be sure to name each new reference table with a logical name to make it even easier to follow in the future.
How to Duplicate a Column in Power BI using Power Query
To create a duplicate of a specific column in Power BI, Launch the Power Query Editor, Right Click a Column Name, and Select Duplicate Column. A new column is generated as a Power Query data transformation step. Double click into the name of the new column to re-name it.
Power Query is our preferred method for duplicating columns because of the better user experience when working with large datasets. However, you can also duplicate a column using a Power BI DAX formula.
How to Duplicate a Column in Power BI using DAX Formulas
You can create a copy of an existing column in Power BI using DAX Formulas. From Power BI Report View, Click on Column Tools on the Ribbon, and select New Column. A formula field will appear. \
The DAX Formula is: New Column = TableName[ColumnName]
The example below shows the New Column button, and a formula that is duplicating a column named City on the CookieSales Table.
Using DAX to create a copy of an existing column is a fast and easy way to create a duplicate column. While the performance of the query may not be as good as Power Query, many datasets are not large enough for it to make much difference for the end user.
Copying Queries Between Different Power BI Reports and Excel
To create copies of tables from one Power BI report to another, Launch the Power Query Editor, Right Click on a Data Table and Select Copy. Then open a separate Power BI file, Launch the Power Query Editor and Click Paste on the list of table names. By copying and pasting queries, you can avoid repeating data prep steps between reports.
You can also copy and paste Queries between Excel and Power BI!
Patrick from Guy in a Cube shows some great tips and tricks for working with Power Query between different files in the following video.
Conclusion
Power BI provides a number of options to create copies of existing tables and columns. Power BI developers can use Power Query or DAX to create duplicate tables. We recommend using Power Query when possible to create a better user experience and off-load the calculation time to a scheduled refresh instead of being dynamic when a user selects a filter or drill down.
To reduce file size or to propagate changes to a sub-query, create a reference table.
Columns can also be duplicated from an existing column by using the New Column DAX function, or you can right click on a column header and duplicate it from the Power Query Editor.