Extract Measures from an Excel Workbook

I was working with a client last week and the unthinkable happened.  The client made a simple editing mistake inside the Measure editing dialogue box and it corrupted the Power Pivot workbook. We could still edit the corrupt workbook, but the Pivot Tables stopped working.  Oh No!!  Now what?  He had written half a dozen new Measures and all that effort now seemed to be lost.

Luckily we had incremental backups of the workbook we were developing.  Well actually it wasn’t luck – it was good forward planning.  Even though Power Pivot is an excellent tool, anyone that uses it will know it is a bit flaky and it can cause problems when editing.  As a precaution I always keep incremental backups of my workbooks when doing data modelling work.  I have optimised this process using an incremental backup macro I created.  You can read about that here and even download the VBA code to use yourself.

OK, but we still had a problem.  We had a backed up copy of the workbook, but this backed up copy didn’t contain the last 30 mins work – about half a dozen DAX formulae.  Power Pivot doesn’t allow you to export the Measures and then reuse them.  But there is a solution using DAX Studio.

What is DAX Studio?

DAX Studio is a great tool that allows you to do many things with your Excel Power Pivot data models.  It will allow you to use DAX as a query language to interrogate your data model and many other things.  One of those “other things” it allows you to do is get a list of all of your DAX Measures in a new sheet in your workbook.

You can read more about DAX Studio and download it here.  Note I installed version 2.2.1 today and it didn’t work for me.  I am currently using version 2.1.1 and that works fine.

Step by Step Instructions on how to Extract Measures

This is how you go about extracting your Measures from an Excel Workbook.

First you need to install DAX Studio. Just download it from the link above.  Don’t run DAX Studio when prompted to do so immediately after the install.  Just exit after the install is finished.  Make sure you have Excel closed when you run the install.

The next step is to open Excel, open your work book and then run DAX Studio.  You will find DAX Studio on the Add-Ins tab on the Excel Ribbon.

When you launch, it will ask you which Data Source you want to connect to.  In this demo I am connecting to a data model in an Excel workbook.  This is what most Excel users will normally do.  It will also work with a tabular instance of SSAS if you have these.

Once connected, you will see a list of tables in your Power Pivot Data model on the left.  The next step is to switch to the DMV tab down the bottom of the screen.

When you have clicked on the DMV tab, you will see a list of objects in the left hand panel.  Locate the one called MDSCHEMA_MEASURES and drag it into the editing pane on the right.

DAX Studio will then automatically write the query required to extract the Measures from the workbook.

Before executing the query you should change the output settings so it sends the results to a clean spreadsheet within your workbook.  When done, click the Run button to execute the query.

After you have run the query, you will notice there is a new sheet in your workbook that contains all the detail of your measures.

But there is a lot of information here that you probably don’t care about.  You can manually clean up the data by deleting what is not important.  You really just need the name and the formula (columns D and N) and maybe a couple of others.  I have written a small VBA macro to clean up the DAX Studio output for this exercise.  Feel free to use this code if you like.  If you don’t know how to use this VBA code ,you can read about how to do that here.

Sub CleanDAXStudioMeasures()
    Application.ScreenUpdating = False
    Columns("A:B").Delete Shift:=xlToLeft
    Columns("C:D").Delete Shift:=xlToLeft
    Columns("C:I").Delete Shift:=xlToLeft
    Columns("D:G").Delete Shift:=xlToLeft
    Columns("E:E").Delete Shift:=xlToLeft
    Columns("C:C").ColumnWidth = 50
    Range("A1").Select
    
    While Selection.Value <> ""
        If Left(Selection.Value, 1) = "$" Then
            Selection.EntireRow.Delete
        Else
            If Left(Selection.Offset(0, 1).Value, 1) = "_" Then
                Selection.EntireRow.Delete
            Else
                Selection.Offset(1, 0).Select
            End If
        End If
    Wend
    Range("A1").Select
    Selection.EntireColumn.Delete
    Range("C1").Value = "Table"
    Range("E1").Value = "Full Formula"
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, xlYes).Name = "Table1"
    Range("E2").FormulaR1C1 = "=RC[-4]&"":=""&RC[-3]"
        Columns("A:E").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

After running the above Macro, you will end up with a table that contains the relevant information including the following full formulae in the last column.

You can simply cut and paste these formula one at a time into the calculation area at the bottom of each table inside the Power Pivot window of you last backed up copy of the workbook.  Note you will then need to manually apply any data formatting you need.