Forum Discussion

abpgupta's avatar
abpgupta
Continued Contributor
5 years ago
Solved

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!        
Need Help
Tips and Tricks
  • edhans's avatar
    edhans
    5 years ago

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

abpgupta's avatar
abpgupta
Continued Contributor
5 years ago

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!

 

 

 

edhans's avatar
edhans
Community Champion
5 years ago

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.