skip to Main Content

 
UPDATE:  Uploaded a new version of the macro file that David provided that fixed a couple of small problems.  Also, I fixed a typo (I had it as XSLM, not XLSM – thanks Dan!)

We have a guest post today from David Hager.  Like most folks who exchange email with me, David is now aware of how… intermittent… an enterprise that is.  Intermittent being the description of the replies one receives.

But he persevered.  And this is a pretty ingenious macro he has written.

So ingenious, in fact, that he is bordering on revealing a few techniques that I have been debating whether or not to unmask.  Things we do at Pivotstream that Microsoft considers unsupported.  Which, of course, is the good stuff.  David is traipsing around in some very dark corners.

The only clarification I will offer to his post, which appears below, is the following:  When David says “this procedure finds where the measures are stored in the PowerPivot workbook,” I want to make clear that what is being found is actually where PowerPivot keeps a backup copy of the measures.  The backup copy is always up to date, so it is very much reliable.

But the “real”definitions are actually stored elsewhere, in a place named Item1.data – and, like Forrest Gump, that’s all I have to say about that.

Take it away David…

Creating a Measures Table From PowerPivot Workbooks in a Folder

By David Hager

When defined name formulas are added to an Excel workbook, they can be easily accessed and viewed through the use of Excel’s Name Manager. However, when measures (formulas added to PowerPivot pivot tables and based on the DAX query language) are created, there is no way to know that they exist except for visually scanning the PowerPivot field list. An icon resembling a calculator will be to the right of each formula created in the field list. However, in order to view the DAX formula, the field must be right-clicked and “Edit Measure” selected. Then, if you wanted to store that measure somewhere for future reference, it can be copy/pasted to the desired location. This process becomes time-consuming if you have many PowerPivot workbooks that contain many measures. It would be nice if that process could be automated. To that end, the following VBA procedure for opening files in a folder and extracting the DAX measures and putting them in a table can be downloaded here.

(NOTE FROM ROB:  You MUST rename that file to .XLSM before you can open it!  WordPress does not allow upload of macro-enabled files, so I uploaded as XLSX.  And if you don’t trust macros from other people, I respect that.  Maybe we’ll upload a text only version with instructions for those of you in that camp.)

Most of the code deals with file and folder manipulation, and will not be discussed here. The core procedure is shown below.

Sub ExtractAndCopyMeasures()
Dim tText As String
Dim strStart As Long
Dim sCopy As String
Dim sCopyArray
Dim aCol As Range
Dim bCol As Range
Dim sItem

On Error Resume Next

tText = ActiveWorkbook.CustomXMLParts("http://gemini/workbookcustomization/MetadataRecovery _Information").XML

strStart = CLng(InStr(1, tText, "CREATE MEASURE"))
sCopy = Mid(tText, strStart, InStr(strStart, tText, "</") – strStart – 2)
sCopy = Replace(sCopy, " ", "")
sCopy = Replace(sCopy, Chr(10), "")
sCopy = Replace(sCopy, "&lt;", "<")
sCopy = Replace(sCopy, "&gt;", ">")
sCopyArray = Split(sCopy, ";")

For Each sItem In sCopyArray

  Set aCol = ThisWorkbook.Worksheets("MeasureTable").Range("a1048576").End(xlUp).Offset(1, 0)
  Set bCol = ThisWorkbook.Worksheets("MeasureTable").Range("b1048576").End(xlUp).Offset(1, 0)
  aCol.Value = ActiveWorkbook.FullName
  bCol.Value = Mid(sItem, InStr(1, sItem, ".") + 1)

Next

End Sub

In essence, this procedure finds where the measures are stored in the PowerPivot workbook, parses the measures and place them in a table. Now, let’s examine some key parts of the code.

tText = ActiveWorkbook.CustomXMLParts("http://gemini/workbookcustomization/MetadataRecovery _Information").XML

This line loads into a variable the XML from the custom XML part in the workbook that contains the measures.

strStart = InStr(1, tText, "CREATE MEASURE")

This is the position in the XML string where the first measure is located.

sCopy = Mid(tText, strStart, InStr(strStart, tText, "</") – strStart – 2)

This code locates and stores just the part of the XML string that contains the measures.

sCopy = Replace(sCopy, " ", "")
sCopy = Replace(sCopy, Chr(10), "")
sCopy = Replace(sCopy, "&lt;", "<")
sCopy = Replace(sCopy, "&gt;", ">")

These lines of code clean up the string so that the formulas can be properly displayed. In particular, the terms "&lt;" and "&gt;" are used in XML for “<” and “>” respectively to prevent reading errors.

sCopyArray = Split(sCopy, ";")

The Split function converts the delimited string into a variant array containing each measure as an item in the array.

For Each sItem In sCopyArray

  Set aCol = ThisWorkbook.Worksheets("MeasureTable").Range("a1048576").End(xlUp).Offset(1, 0)
  Set bCol = ThisWorkbook.Worksheets("MeasureTable").Range("b1048576").End(xlUp).Offset(1, 0)
  aCol.Value = ActiveWorkbook.FullName
  bCol.Value = Mid(sItem, InStr(1, sItem, ".") + 1)

Next

The final part of the procedure loops through the array. aCol is used to set the next empty row without using a counter. The measure is extracted from the array item by using Mid(sItem, InStr(1, sItem, ".") + 1) to start at the correct string postion.

I hope that you find this procedure useful!

Note: There may be some prompts that will have to be answered manually during the file opening process.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 11 Comments
  1. Rob,

    To push this idea further, can PowerStream or Microsoft develop a meta data layer which would extract, document and source control the business logic definition. What if we could also deploy them across environments? Talk about industrialization, manageability, that is it.

    1. Hi DJ. A lot of that is coming in the BISM version of PowerPivot, due later this year. We do have a lot of internal tools today that streamline a lot of authoring for us however.

  2. Nice post, some information on how to actually use this would have been helpful for us now ‘Excel’ people, but figured it out. Explaining the different macros included would be nice as well. The other thing that I wanted to point out was the the ‘Note from Rob’ in read has a typo and should be ‘XLSM’, not ‘XSLM’. I did run one Macro to extract the measures and ended up with an empty Excel file open and then had to reopen the macro file to view the measures that were extracted from my files. — Thanks for sharing.

  3. No luck changing and opening the excel file (changed the file to xlsm). Copied the macro and pasted it in a module that hasn’t helped also. Sigh.

  4. Exactly the tool I need. I cannot get it to work using Win10 64-bit and Excel 2013 64-bit. Any chance for an update? Maybe just some user instructions. Concept is terrific. I do not want to keep rewriting all my DAX formulas, they are way too difficult to create and manage.

  5. Hi guys, I tried to use the macro, but it doesn’t work because this line returns nothing: ActiveWorkbook.CustomXMLParts(“http://gemini/workbookcustomization/MetadataRecoveryInformation”).XML. I’m using Excel 2013. Any clues?? Thanks in advance!

  6. me again! Moreover, I have listed all custom xmls via VBA. None of them has metadatarecover or “create measure” strings inside them. Could someone let me know how i get to metadatarecovery? Thank you!

Leave a Comment or Question