A NSW Government website
Environment and Heritage
Environment and Heritage

Excel settings for power queries

Power queries provide an alternative way to access BioNet data. We have a number of refreshable Excel spreadsheets that contain embedded power queries that enable you to extract data directly from BioNet databases.

 

Issue

Some users are presented with this error message upon opening a pre-existing power query.

A pop-up notification titled "ThreatenedBiodiversity_Species" warns that "External Data Connections have been disabled." It shows the "Last refreshed" date as Thursday, 20 August 2020, and the "Load status" as "Not loaded." Three buttons at the bottom are labelled "VIEW IN WORKSHEET," "EDIT," and "DELETE.

Figure 1: Error message

Solution

Follow these steps to resolve this issue:

  1. In Excel, go to File > Options > Trust Centre > Trust Centre Settings > External Content.
  2. Make sure the security settings for Data Connections, Workbook Links and Linked Data types are set to 'Prompt user …'
  3. Click 'OK'.
Screenshot of the Trust Center settings in Microsoft Excel, specifically the External Content section. Options include security settings for Data Connections, Workbook Links, Linked Data Types, Dynamic Data Exchange, and Microsoft Query files. Various options are available to enable, prompt, or disable these features.

Figure 2: Security settings screen

4. Reopen the power query to apply the security setting changes.

5. Remember to refresh the power query as required by clicking 'Enable Content' and 'Refresh All'.

Screenshot of the Data tab in Excel with the "Refresh All" button highlighted and a security warning about external data connections being disabled. An "Enable Content" button is also visible.

Figure 3: Refresh All tab

More information

All BioNet power queries are available on our BioNet resources page.

For further assistance, please contact the BioNet team at [email protected].

Contact us

BioNet team

Email: [email protected]