Forum Discussion
Modify Query
After getting data from SQL table how can I go back and modify my query?
thanks
Yes.
Click on Transform Datato get into Power Query. Find and click on the SQL query in the list of queries. Now right-click on the last Applied Step on the right, and select "View native query". You will get the *exact* SQL statement that Power BI will issue to the SQL Server to get this data. Notice that things like WHERE clauses are pushed down to the SQL layer, so are GROUP BY and many others.
Only formal RDBMS will support this feature. You can't get it with Excel or CSV or a lot of others. Only SQL, Oracle and a few others.
To execute a stored procedure to get data from Power BI, you need to use the Advanced Options.
14 Replies
You can't. You can open Transformations (Power Query) and add/edit/change the Applied Steps to have it generate a different SQL statement, but you can't set the query directly.
But there are advanced workaround:
Create a blank query with your SQL text enclosed in double quotes like: "SELECT * FROM ..."
Then have another query reference that one with something like this:
let
Source = Sql.Database(localhost, DatabaseName, [Query=#"sql_text"])
in
SourceOh boy. Crazy stuff.
ToddChitt And without doing this can I at least see the underlying SQL?
Yes.
Click on Transform Datato get into Power Query. Find and click on the SQL query in the list of queries. Now right-click on the last Applied Step on the right, and select "View native query". You will get the *exact* SQL statement that Power BI will issue to the SQL Server to get this data. Notice that things like WHERE clauses are pushed down to the SQL layer, so are GROUP BY and many others.
Only formal RDBMS will support this feature. You can't get it with Excel or CSV or a lot of others. Only SQL, Oracle and a few others.
ToddChitt No steps are available. However, clicking on Gear icon in the upper right corner gives me my SQL code.
Markzolotoy , In case you have got data using a query in advance option in connection you can modify that
Or create parameterized
https://community.powerbi.com/t5/Desktop/parameterize-connection/m-p/205900#M90712
Markzolotoy Curious to know if you followed non-advanced steps to connect to a server, database, and table, then build your query (Applied Steps) by adding transformations like Filters, Removal of columns, adding custom columns, or if you followed amitchandak 's screen shot and went for Advanced Options and pasting in your custom SQL code?
In one case you can View Native Query. In the other, you have to get into Advanced Editor. Knowing this at the start would have helped us direct you to the solution quicker.
My original answer assumed you followed the most common path of adding transformations in Power Query, but it sounds like you wrote your own and went with Advanced Options.
ToddChitt I used Advanced option.
Markzolotoy you can surely change the query on the existing one, you need to go advanced editor and change the step and add the query. Check this post.
Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
