I first wrote this article back in Jan-2019 and have decided to come back and update it now. With the recent enhanced External Tools Toolbar in Power BI Desktop, there has never been a better time to start to learn how to use these tools.
Have you ever wondered 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. I am not saying this is the best way to 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 the easiest way to do it.
Tabular Editor Warning!
Ok, here is the warning. Tabular Editor connects direct to your running versions of Power BI Desktop. Things can and do go wrong, so you should create a backup copy of any file before you unleash Tabular Editor on that file. Consider yourself suitably warned 🙂 .
How Do I Do it Already?
First of all you need to open both of your PBIX files in Power BI Desktop. I am using the Aug-2020 version for this demo. You need to turn on the preview feature for enhanced metadata with this version. This is only needed for older versions of Desktop. As of about Sept-2020, this feature is now generally available, so you don’t need to turn it on from preview features. The Report Server version may be different.
You will then have 2 running instances of PBI Desktop running on your PC. Make sure you are clear which is which.
Launch Tabular Editor (Twice)
Go to each version of PBI Desktop, one at a time, and launch 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, then read my article here to find out how to get these.
You should now have 2 instances of Tabular Editor open, one connected to each running instance of PBI Desktop.
Copy the Measures
First you have to make the measures in the source file visible (if you can’t see them).
- 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 (the ones you want to copy, anyway). 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)
- Click save on Tabular Editor in the receiving workbook.
After saving, you should be able to see all the measures in the 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. You can do this directly inside Tabular Editor, or you can do it in Power BI Desktop.
As a general tip, I recommend you use Tabular Editor as follows.
- Open it when you want to use it.
- Use it – exclusively, saving the changes when you are ready.
- When you are done, close it down. You can easily reopen it again later if needed.
Doing it this way makes sure that you don’t get any model conflicts by making changes in both Power BI and Tabular Editor in parallel
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.