Access PowerBI SQL Queries Independent of PowerBI Desktop

[Anonymous]

Hello,

There are times where I need to view a SQL query I wrote, or someone else wrote within a PowerBI desktop file. 

1. Is it possible to view the SQL query within Power Query without downloading and opening the PowerBI desktop file? For the largest reports, this process of downloading and opening can take ~7-8 minutes. 

2. Even if I can't access the SQL through the PowerBI service, is it possible to interface with PowerBI data locally through a standard PBIX file without PowerBI. I know you can create a PBIT file then explore the schema, but that requires you to open the PBIX file to create a PBIT file. Having a quick way to interface/make quick edits without all of the heft of the PowerBI desktop would be much appreciated.

I'm open to exploring more technical solutions, such as interfacing the data programmatically, assuming the solution doesn't have me essentially recreating PowerBI. 

[v-angzheng-msft]

Hi, @Anonymous 

Please follow the steps below.

1. Make a copy of the PBIX.

2. Change the extension to zip

3. Extract the pbix file and then find the DataMashup

4. Extract the DataMashup

5.Find the Formula folder and find the Section1 file and open it with Notepad.

For references:

https://community.powerbi.com/t5/Desktop/Download-Export-all-queries-SQL-statements-from-Power-BI-Desktop/td-p/665731

Other related information:

https://towardsdatascience.com/how-to-capture-sql-queries-generated-by-power-bi-fc20a94d4b08

https://community.powerbi.com/t5/Desktop/See-underlying-SQL-Query/m-p/348515

http://cognettacloud.net/2018/07/27/the-magic-power-bi-button-how-to-edit-your-direct-sql-statements-in-power-bi/

https://blog.crossjoin.co.uk/2020/12/13/capturing-sql-queries-generated-by-a-power-bi-directquery-dataset/

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I do not see a datamashup folder.

[v-angzheng-msft]

Hi, @Anonymous 

Unfortunately, the DataMashup is no longer saved in the pbix. All of the metadata that was in the Mashup is now saved in the data model.

But extracting data from the data model does not seem easy. This is out of the original question, you can try to find relevant information, here are some reference information I can find. Hope this helps.

For references:

https://community.powerbi.com/t5/Desktop/DataMashup-file-no-longer-exists/td-p/1145141

https://community.powerbi.com/t5/Desktop/How-to-decompress-DataModel-from-PBIX-file/td-p/523811

https://recoverit.wondershare.com/file-repair/7zip-open-archive.html

https://www.7-zip.org/recover.html

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HashamNiaz]

Hi @Anonymous !

You can create a Help page on your reports & enlist all the queries there, usually we create a table in SQL and store all the Power Query Native queries there with Report Name, and in Power BI Report we can bind these with their respective Report Name, so Help page can show all the queries for developer. You can restrict this page access to only developer.

This way you don't need to download Report or swtich to Edit mode, you can simply view queries right within the Report. You need to manage that you will keep your table updated with new definitions. 

Regards,

Hasham

[Anonymous]

I wish it could be more dynamic, thanks for the response. 

[TomMartens]

Hey @Anonymous ,

unfortunately, this is not possible, as the SQL is not exposed.

Regards,

Tom