Forum Discussion

Markzolotoy's avatar
Markzolotoy
Icon for Impactful Individual rankImpactful Individual
5 years ago
Solved

Modify Query

After getting data from SQL table how can I go back and modify my query?    thanks
Need Help
  • ToddChitt's avatar
    ToddChitt
    5 years ago

    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's avatar
    ToddChitt
    5 years ago

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

ToddChitt's avatar
ToddChitt
Icon for Super User rankSuper User
5 years ago

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.

  • Markzolotoy's avatar
    Markzolotoy
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    ToddChitt I used Advanced option.

  • Markzolotoy's avatar
    Markzolotoy
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

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

    • ToddChitt's avatar
      ToddChitt
      Icon for Super User rankSuper User
      5 years ago

      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?

      • Markzolotoy's avatar
        Markzolotoy
        Icon for Impactful Individual rankImpactful Individual
        5 years ago

        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?