I have often been asked (and wondered myself) if it were possible to move all the measures from one Power BI PBIX file into another. Imagine you have 2 PBIX files with similar data models (similar enough that the measures will be substantially the same). It doesn’t matter how you got to this point – the point is if this is you, there is a hell of a task to move the measures over, one by one.
Edit 12 Feb 19: Note, I am not saying this is how you should create a new similar workbook, I am just saying if you find yourself in a situation where you have measures in one workbook and need them in another workbook, then this is currently the easiest way to do it. If you have 1 workbook and you want to create a new variant of essentially the same workbook, simply select File\SaveAs and create a copy of your master and go from there.
Recently someone (maybe Gilbert at fourmoo.com?) suggested that it could be done using Tabular Editor. I couldn’t work out how to do it myself so I asked Marco Russo and he explained it to me. This article describes how to do it yourself.
Ok, here is the warning. This is not supported by Microsoft. If you do this and it breaks your model, you will not get support from Microsoft (or me for that matter). So, back everything up and keep the backups – don’t delete them. Consider yourself suitably warned 🙂 .
How Do I Do it Already?
Create Templates of your 2 workbooks
The first step involves creating templates of your 2 workbooks. A template file contains all the structure of the data model including the connection strings to the data, but none of the data itself, nor the reports. You can later create a new workbook from this template file.
- Open the workbook that contains the measures (I will refer to this as source.pbix)
- Save the file as a template File\Save As\Power BI Template File.pbit
- Open the other PBIX file – the one to which you want to move the measures.
- Save this file as a template too.
Open Tabular Editor (Twice)
- Download Tabular Editor https://www.sqlbi.com/tools/tabular-editor/
- Open an instance of Tabular Editor.
- Connect to the source template file.
- When you do this, you will get a warning like this. Remember my warning above?!
- Open another instance of Tabular Editor and connect to the destination template file.
You should now have 2 instances of Tabular Editor open, one connected to each template file.
Copy the Measures
First you have to make the measures in the source template visible.
- In Tabular Editor, from the view menu (1), select Measures (2). Note the measures are visible (3 below).
- Multi select all measures from the list. Select the first one, hold Shift and click the last one.
- Copy the measures (Ctrl+C)
- Switch to the second instance of Tabular editor
- Select the destination table to store the measures.
- Paste the measures (Ctrl+V)
- Save the template
Open the Destination Template
Now the measures have been copied it is simply a matter of opening the destination template in Power BI Desktop. You can either double click on the template file or open it from the file from the file menu (select templates as the file type). I will refer to this file as the new destination workbook.
You should be able to see all the measures in the new destination workbook.
Mismatch in Table and Column Names
One thing that could happen is that you have slightly different column and or table names in your destination file. I tested this in 2 ways.
- I changed the name of the Sales table to Sale and repeated the copy.
- I renamed one of the columns in the Sales table from ExtendedAmount to ‘Extended Amount’
In both cases the measures copied across OK, I just had to edit the measure to correct the table/column name for the new names.
What do you think? Is this a useful trick? How much time will it save you? How did you get into the situation where you needed to copy measures? Please share your comments below.