cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Markzolotoy
Post Prodigy
Post Prodigy

Modify Query

After getting data from SQL table how can I go back and modify my query? 

 

thanks

2 ACCEPTED SOLUTIONS

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.

View solution in original post

To execute a stored procedure to get data from Power BI, you need to use the Advanced Options.

View solution in original post

13 REPLIES 13
parry2k
Super User III
Super User III

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





amitchandak
Super User IV
Super User IV

@Markzolotoy , In case you have got data using a query in advance option in connection you can modify that

AdvanceProperty.png

 

Or create parameterized

https://community.powerbi.com/t5/Desktop/parameterize-connection/m-p/205900#M90712



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

If I use steps you are providing will my published report get data at real time when I refresh it?

@Markzolotoy 

Not sure what you are asking here.

If you split your query into a SQL Text query, and one that references it to make the call to the database, in the end it behaves exactly like if you had used the Advanced Options. It issues the SQL Query at the time of refresh. 

Does that help?

@ToddChitt If I am not choosing Advanced option it gives me a list of tables to get data from but I need to execute a SP. How can I do that?

To execute a stored procedure to get data from Power BI, you need to use the Advanced Options.

View solution in original post

@ToddChitt I used Advanced option.

Markzolotoy
Post Prodigy
Post Prodigy

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

View solution in original post

@ToddChitt No steps are available. However, clicking on Gear icon in the upper right corner gives me my SQL code.

Markzolotoy
Post Prodigy
Post Prodigy

Oh boy. Crazy stuff.

ToddChitt
Post Prodigy
Post Prodigy

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
Source

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors