Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abpgupta
Post Patron
Post Patron

Way to get all SQLs in Power Query Editor

Hi All

 

Is there any way to get all SQLs from Power Query Editor instead of going through each query and validate Transforms to get final queries.

 

Thanks for inputs here!

 

 

 

 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

If you want to trap all native queries sent, capture them from the SQL Server @abpgupta using a Server Side Trace.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

11 REPLIES 11
edhans
Super User
Super User

You can add that as an idea here @abpgupta 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
abpgupta
Post Patron
Post Patron

I think, PBI should provide ability to generate all SQLs it's sending to database similar to Cognos. 

edhans
Super User
Super User

If you want to trap all native queries sent, capture them from the SQL Server @abpgupta using a Server Side Trace.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User
Super User

@abpgupta - What do you mean by "all SQLs"? SQL tables? Stored procedures? ?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,
I meant to capture all Native Queries sent to Database while running refresh.

Thanks!

Hi, @abpgupta 

If you are querying a relational database, especially SQL Server, then you want to make sure that query folding is being applied. Query folding is when M code in PowerQuery is pushed down to the source system, often via a SQL query. One simple way to confirm that query folding is working is to right click on a step sand select View Native Query. This will show you the SQL query that will be run against the database.

2.png

You can also view related queries through SQL Server Profiler.

4.png

5.png

 

For more details,please check the part of this document about determining-the-queries-sent-by-power-bi-desktop.

 

Best Regards,
Community Support Team _ Eason

Johanno
Responsive Resident
Responsive Resident

Hi, do you mean you want to make the same transformation to several queries? Then I guess you can use Advanced editor and copy paste the code? I think we would need more information of the sources and final results.

Thanks @Johanno  for replying!

 

Yes, I have mutilple Tables and Transformation done in Power Query. To get native queries, I have to go one by one and look for final Native Query to get actual fields used in Report.  I am looking Automated way to get all native SQL queries from available queries in Power Query Editor. 

 

I am using import method for IBM Db2 database.

 

Thanks!

 

 

 

 

You have to get the native query one by one @abpgupta - but I am not sure what you are trying to accomplish? What do you mean "get all fields used in the report.?"

If you want to know all fields in each query, you can use Table.Profile(#"Previous Step Name") to get all fields from each query, but that is still one at a time, and you need to delete that step when done as it cause problems if you close and load that data.

 

You could use Dax Studio and the Vertipaq Analysis tools on the Advanced/View Metrics tab and that will show you all tables and columns loaded in the model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi @edhans ,

 

Some of the use cases:

  • To provide all queries sent to Database with their performance
  • To gather information for reports impacted by a DB table/column changes

In Cognos, we have option to generate all Queries in one go to collect such information. I am looking any way to automate this in Power BI Desktop as well since there is no such option currently available.

Thank you for looking into it!

 

 

 

You can try using Power Query's diagnostics. Go to the Tools tab, Start Diagnostics, then refresh all. When done, stop the diagnostics and review the queries it generated. There will be a LOT of SQL code there if you are taking advantage of native queries. You'd have to play with it to find the final query for a given table.

 

Note it may look like this:

select top 1000
    [_].[item_no],
    [_].[bin_no],
    [_].[qty_on_hand]
from [dbo].[IMINVBIN_SQL] as [_]
where [_].[loc] = 'MAN' and [_].[qty_on_hand] > 0

Just remove the "Top 1000" to get your query. PQ only pulls the top 1,000 records internally. The final load removes that step.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.