Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Markzolotoy
Impactful Individual
Impactful Individual

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

14 REPLIES 14
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
Super User

@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

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

@ToddChitt I used Advanced option.

Markzolotoy
Impactful Individual
Impactful Individual

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

Thank you very much, simpler it could not be!

Markzolotoy
Impactful Individual
Impactful Individual

Oh boy. Crazy stuff.

ToddChitt
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.