Remove data from the external data range grayed

Question

How do I Remove the data?

Answer 1

Hi theking2, 

Welcome to the Microsoft community. 

I am sincerely grateful to you for the patience with which we are considering this issue. Thank you for choosing Microsoft, and we appreciate your support. 

As far i known, the image you provided shows the Query Properties window for the "Salesforce Full" query in Excel on Windows. The option to "Remove data from the external data range before saving the workbook" is grayed out, which typically happens when the query is set to "Connection only" or when certain conditions prevent data from being cleared (e.g., the query isn’t loaded as a table or the workbook is protected). This option is meant to delete the query’s data from the worksheet when saving, but since it’s disabled, you’ll need an alternative method to remove the data.  

Below are the steps to remove the data associated with this query on Windows. 

Check the Current Load Status 

• Open Queries & Connections:  
  • Go to the Data tab > Queries & Connections.
  • In the Queries pane, right-click "Salesforce Full" and select Properties.
  • Confirm if "Enable Load" is checked and where it’s loaded (e.g., "Connection only" or a specific table).
• If Connection Only:  
  • If it’s "Connection only," no data is in a sheet to remove, but the query definition remains. Proceed to delete the query (Step 3) if you want to fully remove it.

Clear Data from the Worksheet (If Loaded) 

If the query data is already loaded into a sheet (e.g., as a table): 

• Locate the Table:  
  • Check the sheets for a table linked to "Salesforce Full" (it might be on a new sheet or an existing one).
  • The table will have filters and a connection icon.
• Delete the Table:  
  • Select the entire table (click the top-left corner or use Ctrl + A within the table).
  • Press Delete to remove the data.
  • Alternatively, right-click the table, choose Table > Convert to Range, then delete the range.
• Unlink the Query:  
  • Go back to Queries & Connections, right-click "Salesforce Full," and select Properties.
  • Uncheck "Enable Load" to prevent future data loading, then click OK.

Delete the Query Entirely 

If you want to remove the query and its connection (including any cached data): 

• Delete from Queries & Connections:  
  • In the Data tab, click Queries & Connections.
  • Right-click "Salesforce Full" in the Queries pane.
  • Select Delete, then confirm. This removes the query definition and any associated connection data.
• Remove External Connections:  
  • Go to Data > Connections (in the Connections group).
  • Select the "Salesforce Full" connection, click Remove, and confirm.
  • This deletes the external data link, ensuring no data is retained.

Clear Cached Data 

Even after deleting the query, some data might remain cached: 

• Clear Query Cache:  
  • Go to Data > Get Data > Query Options.
  • Under Global > Data Load, click Clear Cache and confirm.
  • This removes any residual data stored by Power Query.
• Save and Reopen: Save the workbook, close it, and reopen to ensure all data is cleared.

Prevent Future Data Retention 

To avoid this issue in the future: 

• In the Query Properties window, uncheck "Enable background refresh" and "Refresh data when opening the file" if you don’t need automatic updates.
• Ensure "Remove data from the external data range before saving the workbook" is checked when loading data as a table (it should become available after changing to a table load).

Something you can noted: 

Option Still Grayed Out:  

• If the option remains grayed after loading as a table, the workbook might be protected. Go to Review > Unprotect Sheet or Unprotect Workbook (enter the password if prompted).
• Ensure the query is loaded as a table (not "Connection only") by using Close & Load To… and selecting a table destination.

Data Persists:  

• Check for hidden sheets or tables. Use Ctrl + F to search for "Salesforce" in the workbook.
• Use Data > Get & Transform Data > Manage Queries to verify no duplicate queries exist.

Error on Deletion:  

• If you can’t delete the query, close any open connections to Salesforce via Data > Connections, then retry.

If the "Remove data from the external data range before saving the workbook" option remains grayed out after loading as a table, it might be a limitation of the query’s current state—deleting the query is the most reliable solution. Let me know if you encounter issues or need further assistance! 

We look forward to your reply. We will continue to assist you if you any other question or based on the information you provide. I sincerely appreciate your patience and cooperation.     

If my answer is helpful, please mark it as an answer by Yes, which will definitely help others in the community who have similar queries to find solutions to their problems faster. 

Thanks and Have a good day!!! 

Best regards.      

Sting-Ng - Microsoft Community Support Specialist.