Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
If you want to trap all native queries sent, capture them from the SQL Server @abpgupta using a Server Side Trace.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou can add that as an idea here @abpgupta
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI think, PBI should provide ability to generate all SQLs it's sending to database similar to Cognos.
If you want to trap all native queries sent, capture them from the SQL Server @abpgupta using a Server Side Trace.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@abpgupta - What do you mean by "all SQLs"? SQL tables? Stored procedures? ?
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.
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans ,
Some of the use cases:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |