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.

11 Replies

  • Johanno's avatar
    Johanno
    Continued Contributor
    5 years ago

    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.

    • 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 Greg_Deckler ,
      I meant to capture all Native Queries sent to Database while running refresh.

      Thanks!

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        5 years ago

        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.

        You can also view related queries through SQL Server Profiler.

         

        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

  • edhans's avatar
    edhans
    Community Champion
    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

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