XMLA dataset tables created with dataflow do not have M code

[olegkazanskyi]

We want to pull M code from Power BI Service to get information that we can't with using API, like a specific stored procedure used in a table, all appends and merges etc.

This code shows all dataset M Queries

select * from
$system.discover_m_expressions

But when I run it for a dataset built completely from dataflows, it doesn't show anything!

Is it a bug?

[bcdobbs · Accepted solution]

Hi,

That returns any shared expressions in M which I thought were things like parameters and functions.

In tabular for modern data sources like data flows the M expressions are stored in the table partition which can be accessed with:

SELECT *
FROM $SYSTEM.TMSCHEMA_PARTITIONS

[olegkazanskyi]

It works!

Thank you!

[bcdobbs]

No problem, I've learnt something new there so thank you!

Following code gives you list of all those queries:
SELECT * FROM $System.DBSchema_Tables
WHERE TABLE_TYPE = 'SCHEMA'
ORDER BY TABLE_NAME ASC

which is from Dynamic Management Views (DMVs) in Analysis Services | Microsoft Docs

[olegkazanskyi]

You literally changed my life today! 

I wondered where all these requests come from, now I know!

I will dig deeper!

Big kudos!

[bcdobbs]

If you open your XLMA end point in tabular editor you can see the struture:

and then the data source M is in the tables Partition: