Edit Setting / Gear button (next to Source) is not visible

[DataSpace]

Hi,

I have implemented incremental refresh where data model uses a complex query (not table) to refresh. parameters are being used within the query so that it can take configured values as per settings.

When I am checking query in Power BI Query Editor, Edit Setting button ( Gear button) next to 'Source' under 'Applied Steps' is not at all visible.

1. Any clue why it is so?

2. What is alternate way to see/copy/check SQL query instead of M code window?

[Shravan133 · Accepted solution]

When using incremental refresh in Power BI with complex queries and parameters, you might run into situations where the Edit Settings button is not visible. This can happen for several reasons:

1. Reason for Missing Edit Settings Button

1. Direct Query or Parameterized Queries:

   • If your data source is set up to use Direct Query or if the query itself is parameterized, the Edit Settings button may not be available in the Query Editor. This is because Power BI is not able to directly edit the source settings for complex queries or Direct Query sources.

2. Complex M Queries:

   • For complex M queries or custom SQL queries, the Edit Settings button might be hidden because the query is not directly editable in the same way as standard queries.

3. Permission Issues:

   • Sometimes, permission issues or access rights to the data source might affect the visibility of the Edit Settings button.

2. Alternatives to View/Copy/Check SQL Queries

If the Edit Settings button is not available, you can use the following methods to view or copy your SQL queries:

Using Advanced Editor in Power BI Query Editor

1. Open Advanced Editor:

   • In Power BI Desktop, go to the Home tab and click on Transform Data to open the Query Editor.
   • In the Query Editor, go to the Home tab and click on Advanced Editor.

2. View M Code:

   • The Advanced Editor allows you to view and edit the M code for your query. This includes the SQL queries if they are embedded within the M code.
   • You can copy and review the SQL query here, but remember that if you’re using complex parameters, you might need to check how they are being used within the M code.

Using SQL Profiler or Trace

1. SQL Profiler (For SQL Server):

   • If your data source is SQL Server, you can use SQL Profiler to capture the SQL queries being executed against your database.
   • Start SQL Profiler, set up a trace, and monitor the queries being executed while refreshing your data in Power BI.

2. Database Trace:

   • For other databases, you might have similar tracing tools or logs that capture executed SQL queries.

Reviewing Data Source Settings

1. Check Data Source Settings:
   • In Power BI Desktop, go to the File menu, then Options and settings > Data source settings.
   • This might give you insights into the connection details and parameters being used, though it might not show the full SQL query.

Editing Parameters Directly

1. Manage Parameters:
   • In the Power Query Editor, go to the Manage Parameters section. Here, you can view and modify parameter values that affect your queries.
   • If you are using parameters within your query, this might give you some clues about how the SQL queries are being constructed.