topic How to extract the M Query meta data from Datamashup file via Powershell in Developer

How to extract the M Query meta data from Datamashup file via Powershell

NandanHegde — Tue, 05 Apr 2022 09:15:58 GMT

Hey,

We are able to manually extract the Power query data from the Datamashup file through 7-zip tool.

Is there any way we can automate the extraction from Powershell?

Re: How to extract the M Query meta data from Datamashup file via Powershell

V-lianl-msft — Fri, 08 Apr 2022 02:41:14 GMT

Hi NandanHegde ,

See if this will help:

https://github.com/cporteou/PowerBI-Metadata

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to extract the M Query meta data from Datamashup file via Powershell

NandanHegde — Fri, 08 Apr 2022 04:18:29 GMT

Hey,

Unfortunately the above Github code is for other PowerBI detals.

My requirement is to extract the source queries like say Select * from table in case if SQL is the source.

And it seems MSFT has removed the datamashup file from PBIX.

So is there any other way to get the source quuery details

Re: How to extract the M Query meta data from Datamashup file via Powershell

SpartaBI — Thu, 28 Apr 2022 13:16:33 GMT

Hi NandanHegde,

You have a couple of options:
1. PBIX files include a backup of the AS database. In order to read the M queries you can restore the AS database either to a local AS server instance (by programmatically opening the PBIX file using Power BI Desktop then finding the localhost AS server port number) or to the Power BI Service (which can be done using APIs, or it may already be published...).

You can access an AS database using the AMO-TOM assemblies, which are built on .Net.
In order to connect to an AS database, you will use the XMLA endpoint. So for the Power BI service option, you will need to use premium (not needed for the local AS server instance option).

2. If the AS database is deployed to the Power BI Service, you can also get the M queries by using the Scanner APIs. This option is not limited to premium and does not require the AMO-TOM assemblies.

3. Another option is to convert a PBIX file to a PBIT file using Power BI Desktop. The latter contains the tabular model json representation. You can parse it to an object using ConvertFrom-Json (or using the AMO-TOM assemblies) and extract the M queries.