How to identify if a table is being used in any power bi dataset?

[algunn14]

My client is deprecating a few database tables and they want to know if any of the tables are being used in any of the 500+ power bi reports on server. 

Is there a way to pull a list of all source tables being used in all datasets? Even if I could just get the m-code for each table this would be a huge win.

I have admin access and can run cmdlets etc.

[MelVL]

@algunn14  just wondering if you ever solved this one?  We have a number of database table changes occuring and are also finding it difficult to trace the impacts, without manually pulling down the code from hundeds of power BI reports.

[Anonymous]

Hi Greg_Deckler , R1k91 ,thanks for the quick reply.

Hi algunn14 ,

After my search, maybe you can use 'Power BI Cleaner' to see which tables are used in each report, which is indirectly for you.

exceleratorbi.com.au

[Greg_Deckler]

@algunn14 Powerops, powerops.app can allow you go get the M code for every query in a report. Maybe something like this: Getting a list of Power BI reports and Data Sources from the Power BI API – SQL Ryan

[algunn14]

@Greg_Deckler  I appreciate the quick response! Unfortunately, it looks like the link you shared does not provide the actual table names / m-code, it just provides the data source connnection type (sql, xls, csv etc).

[R1k91]

something like this? https://medium.com/riccardo-perico/bulk-extract-m-code-from-power-bi-datasets-49e8f4a236f9

[algunn14]

@R1k91I think this is on the right track. I've very interested in the bottom of this article which talks about looping through all reports to pull out m-code for each.

My client has hundreds of reports and I am in charge of identifying all reports that are using a particular database. The client is moving away from this database and needs to know which reports are impacted so they can create mapping to the new sources.

I've tried using DMVs in Dax Studio like select * from $SYSTEM.DISCOVER_M_EXPRESSIONS but this only pulls from 1 report at a time which would take a ton of time, and in many instances it will completely miss some queries (only pulls results for 4 of 8 queries).