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

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!

 

 

 

 

edhans's avatar
edhans
Community Champion
5 years ago

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.

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