cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
juliusj
Frequent Visitor

Access PowerBI SQL Queries Independent of PowerBI Desktop

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. 

6 REPLIES 6
v-angzheng-msft
Community Support
Community Support

Hi, @juliusj 

 

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-De...

 

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...

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

 

 

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.

 

Annotation 2021-06-17 083536.jpg

 

I do not see a datamashup folder.

Hi, @juliusj 

 

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
Solution Sage
Solution Sage

Hi @juliusj !

 

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

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

TomMartens
Super User II
Super User II

Hey @juliusj ,

 

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

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.