Leniel Maccaferri's blog: Automate moving measures between Power BI files .pbix, renaming and finding and replacing strings in DAX formula\expression

Yesterday I had to accomplish a task that got me thinking: this is going to take a hell of a lot of time to finish.

The user story is this: I have a Power BI .pbix file that is used somewhat as a staging\debugging file to try and test measures. Once the job is done I usually move things over to the final\main Power BI file which will be handed in to the client. Note that this final Power BI file already has report pages implemented. That's way I try things in a different file since the work can be done in a cleaner\faster model.
The problem this time was that I had to create\move lots of measures and so I'd have to do this measure by measure from the debugging file to the main file. It would take me about 1 hour to finish. Unfortunately Power BI has no built in copy\paste of measures between files as of today in its version 2.72.5556.801 64-bit (August 2019). At least we can copy and paste the visuals from one file to the other.

OK. Problem defined. Let's Google for a solution. The solution found was this awesome post titled Bulk measure handling (copy between reports/format changes). In this post the guy mentions the tool Tabular Editor. Well that was what I was looking for, really.

Just downloaded it directly from its GitHub repo here.

Followed the steps on that post and voilá: I could get more than 30 measures copied from one .pbix file to another. Great! This took me about 10 minutes to get done. 1\6 of the time previously estimated for that cumbersome\manual work. Check the following printscreen:

1st printscreen - measures moved from one Power BI file (on the left) to the other Power BI file (on the right)

2nd printscreen - batch renaming selected formulas in blue

There was another task that needed to be accomplished and it was a little bit different: needed to duplicate those copied measures. Tabular Editor makes it easy too. Just select all of them, right click and pick Duplicate # measures. Boom! You have all measures duplicated as seen in the printscreen on the right. Note that it adds a "copy" word to the measure name. So once the measures are duplicated we need to rename them and that's easy too. Select all duplicated measures, right click and pick Batch rename.

Just replaced the uppercase U in each formula name to a uppercase C as seen in the second printscreen above. Repeated the process and replaced " copy" with a blank string. That's it. Measures duplicated and renamed accordingly. Nonetheless there's still a missing step before theses measures are ready to go: change a table column name in the DAX formula\expression in each of these measures that got duplicated and renamed. How to get this done using Tabular Editor? Well, reading through its docs we can find this Advanced Scripting section. If you look at the first printscreen above you'll note that I wrote some C# code there that does exactly what is needed. See the code below:

Selected.Measures.ForEach(m => {
var e = m.Expression;

if(e.Contains("[Unit]"))
{
e = e.Replace("[Unit]", "[Company]");

// For debugging purposes
//e.Output();

m.Expression = e;
}
});

What this piece of code is doing is that for each of the selected measures in blue on the left (see printscreens above), the measure DAX formula\expression is stored in a variable called e. I then check if this formula contains the word [Unit] that is the table column name that needs to be replaced. If the expression\formula contains this word it gets replaced with the word [Company] that refers to the other column name that is to be used in these duplicated formulas. After that the modified DAX formula is assigned back to the measure.

When finished with Tabular Editor, click Save. Return to Power BI and hit the Refresh button to reload the model.

This post is just a sample that showcases the power of Tabular Editor. You can manage and play with measures in any way imaginable.

I hope it helps someone that wants to accomplish trivial things in a more actionable way instead of having to copy\paste, rename and replace manually because that sucks.

Note: when trying to save back the modifications done in Tabular Editor an error was being displayed. Something along this line:

Failed to save modifications to the server. Error returned: 'Unexpected column name: Received column 'ObjectID.Expression' in rowset 'ObjectTranslations'. Expected column 'ObjectID.Set'.'.

I did the following to get rid of the nasty error message: opened Tabular Editor File menu and selected Preferences... checked "Allow unsupported Power BI features (experimental)". When we do this a new Translations folder appear in the Model tree. I just deleted the en-US that was under this folder (see printscreen below). Then tried saving again and it worked.