I first wrote this article in January 2019 and have decided to go back and update it now. With the recently enhanced external tools toolbar in Power BI Desktop, there has never been a better time to start learning how to use these tools.
Ever wonder if it was possible to move all measurements from one Power BI PBIX file to another? Suppose you have 2 PBIX files with similar data models (similar enough that the measurements are substantially the same). No matter how you got to this point, the point is that if it is you, there is a big task to change the measurements one by one. I am not saying that this is the best way to create a new similar workbook, I am just saying that if you are in a situation where you have measurements in one workbook and you need them in another workbook then this is the easiest way to do it.
Tabular editor's warning!
Ok, here is the warning. Tabular Editor connects directly to your running versions of Power BI Desktop. Things can and do go wrong, so you should create a backup of any file before activating the Tabular Editor on that file. Consider yourself suitably warned 🙂.
How do I do it already?
First, you need to open your two PBIX files in Power BI Desktop. I am using the August 2020 version for this demo. You must enable the preview feature to get improved metadata with this release. Older versions may work differently.
You will then have 2 instances of PBI Desktop running on your PC. Make sure you are clear about which is which.
Start the tabular editor (twice)
Go to each version of PBI Desktop, one at a time, and start the Tabular Editor. Do it carefully, one at a time, making sure you know which version belongs to each PBIX file.
If you don't see the external tools or tabular editor, read my article here to find out how to get them.
You should now have 2 instances of Tabular Editor open, one connected to each running instance of PBI Desktop.
First you have to make the measurements visible in the source file (if you can't see them).
- In the Tabular Editor, from the View menu (1), select Measures (2). Please note that the measurements are visible (3 below).
- Make a multiple selection of all the measures in the list (the ones you want to copy, anyway). Select the first one, hold down the Shift key, and click the last one.
- Copy measurements (Ctrl + C)
- Switch to the second instance of the tabular editor
- Select the destination table to store the measurements.
- Paste measurements (Ctrl + V)
- Click save in Tabular Editor on the receiving workbook.
After saving, you should be able to see all the measurements in the destination workbook.
Discrepancy in table and column names
One thing that could happen is that you have slightly different table or column names in your destination file. I tested this in 2 ways.
- I renamed the table from Sales 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 to OK, I just had to edit the measure to correct the table / column name for the new names. You can do this directly within Tabular Editor, or you can do it in Power BI Desktop.
As a general advice, I recommend that you use the Tabular Editor as follows.
- Open it when you want to use it.
- Use it - exclusively, saving your changes when you're ready.
- When you're done, close it. You can easily reopen it later if necessary.
Doing it this way ensures that you don't get any model conflicts when making changes in Power BI and Tabular Editor in parallel
What you think? Is this a useful hack? How much time will it save you? How did you get to the situation where you needed to copy measurements? Please share your comments below.