Extracting All The M Code From A Power BI Dataset Using The DISCOVER_M_EXPRESSIONS DMV

DMVs (Dynamic Management Views) are, as the Analysis Services documentation states, “queries that return information about model objects, server operations, and server health”. They’re also available in Azure Analysis Service, Power BI and Power Pivot and are useful for a variety of reasons, for example for generating documentation.

Several as-yet undocumented DMVs have appeared in Power BI recently and one that caught my eye was DISCOVER_M_EXPRESSIONS. Unfortunately, when I tried to run it in DAX Studio against an open Power BI file I got an error saying it was only available in the Power BI Service:

image

Luckily, now that XMLA Endpoints are now in preview and SQL Server Management Studio 18 has been released (which supports connections to Power BI via XMLA Endpoints) we can test it against a published dataset stored in a Premium capacity. The following query can be run from a DAX query window in SQL Server Management Studio:

select * from
$system.discover_m_expressions

…returns a list of all the Power Query queries  in the selected dataset and their M code:

image

If you don’t have Premium you can run the same query from an Excel table against any published dataset using the technique I blogged about here:

image

I know there are other methods for doing this (for example using copy/paste) it’s useful to be able to do this via a DMV because it means you can automate the process of extracting all your M code easily.

Some of the other new DMVs look like they are worthy of a blog post too – I can guess what most of them do from their names, but others are more mysterious and perhaps hint at features that have not been announced yet.

7 responses

  1. Hi Chris!

    Compelling article. I’m curious how can this technique be adapted to work with Dataflows and mapping ot source data fields to target fields in target entities within a Dataflow?

    Any thoughts or comments?

    Respectfully,

    Darryll Petrancuri

    • I don’t think it can be adapted to work with dataflows, because DMVs are a feature of the SSAS engine and dataflows are not part of that (whereas the M queries inside a dataset are). I don’t know for sure though.

  2. Pingback: Extracting All The M Code From A Power BI Dataset Using The DISCOVER_M_EXPRESSIONS DMV | Pardaan.com

  3. Hi Chris!
    And if I want to do the opposite???
    Protect the code so that it can not be extracted.

    Because, if I write:

    select * from $ system.mdschema_measures

    I can also extract the DAX measurements

    Is there a possibility to protect the code?

    Thanks a lot

  4. Thanks a lot for this information Chris !
    I checked the documentation but I didn’t find “DISCOVER_M_EXPRESSIONS”. Could you precise where it is please ?
    Waiting impatiently to have the same DMV to access to the desktop.
    Chears
    Didier

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: