Manage data source settings and permissions (Power Query)

Be careful sharing a workbook with other people who might want to refresh the data or make changes to it. Depending on how the data source credentials are set up, they may need to request access to the data source to avoid refresh errors. Furthermore, depending on the privacy setting, they may not be able to view sensitive data, such as salaries or project release dates. This is particularly important when collaborating with a workbook and making changes to it. When an occasional data conflict occurs, usually the first person to make the change wins. But if the data is refreshed later, those changes may be lost. For more information, see Share external data by using an ODC file.

Excel maintains a list of recently used data sources as a convenience for you to locate and manage them when that’s required.

1. Do one of the following: 
   
   In Excel    Select Data > Recent Sources.
   
   In Power Query Editor    Select Home > Recent Sources.
   
   The Recent Sources dialog appears.

   

2. Select a data source from the list, and then select Connect.
   
   The Navigator dialog box appears. Each data source has a different set of options.
   
   Tip    Right click a data source in the list to perform additional commands, such as Copy the path or manage pin entries.

1. To display the Data Source Settings dialog box, do one of the following:

   In Power Query   Select File > Home > Data Source Settings.
   
   In Excel    Select Data > Get Data > Data Source Settings.

2. Select one of the following:  

   Data sources in current workbook This is the default option and it also displays the Change source button at the bottom.
   
   Global permissions To change permissions for all your workbooks.

3. If there are many data sources, enter a search phrase in the Search data source settings box or select the Sort   button on the right to sort by the name or kind of data source.

4. To change a data source, select it, select Data Source, and then make changes in the dialog box for that data source. This is the same dialog box you see when you first imported the data. Each kind of data source has a different dialog box. 
   
   Tip    For a database with a Native Database Query (such as SQL), you can only view the SQL statement from this dialog box. For more information on how to edit the SQL statement, see Import data from a database using Native Database Query (Power Query).

5. Using the Clear Permissions/Clear All Permissions button, you can clear permissions for the selected data source or for all data sources. Be careful using this command. Once the permissions are cleared, you can’t get them back.

6. To display the Edit Permissions dialog box, select a data source from the list, and then select Edit Permissions.

7. Under Credentials, notice that the permission type of the data source is identified, such as Anonymous. 

8. To remove the permission, select Delete. The permission type now says Not Specified.

9. To add or change a permission, select Edit.

10. In the dialog box left pane (colored green), select the credential type you want: 
    
    Note    The password is not encrypted when sent. Some data sources do not support specific credential types.

    Anonymous    Anyone can use the data source and no credentials are required.
    
    Windows    Enter your Windows username and password.
    
    Basic    Enter a username and password.
    
    Web API    Enter a key required to access the Web source which sometimes requires pre-registration.
    
    Organizational account    Sign in to your work or school account.

1. To display the Query Options dialog box, do one of the following:

   In the Power Query Editor    select File > Options and Settings > Query Options.
   
   In Excel    Select Data > Get Data > Query Options.

2. Select Security under GLOBAL in the left pane.

3. Do one or more of the following in the right pane: 

   Under Native Database Queries   Select or clear Require user approval for new native database queries.
   
   Under Certificate Revocation Check    Select or clear Enable certificate revocation check.
   
   Connections to an HTTPS data source may be insecure if the certificate for the connection was compromised, The revocation check ensures that certificates known to be compromised can't be used. This check may fail for self-signed certificates or certificates used for proxying connections. If those certificates cannot be given a proper CRL Distribution point, then this check should be disabled. Otherwise, this check should be enabled.
   
   Under Web Preview Warning Level, select:
   
   None    Never display a warning before showing a web preview.
   
   Moderate    Display a warning before showing a web preview if the URL hasn't been entered explicitly or approved as trusted during the session.
   
   Strict    Always display a warning before showing a web preview.
   
   Under Approved ADFS Authentication Services, note the following:
   
   When you login, you are sometimes prompted to approve an unknown authentication services. A list of approved unknown authentication services is displayed. If you delete an entry from this list, you remove the approval and will be prompted again on login.
   
   If there is no approved list, you see the message: “You have not approved any authentication services on this computer.”

For Excel Services, use the Excel Services Authentication Settings dialog box:

1. Select Data > Queries & Connections.

2. Select the Queries tab to display the queries in the workbook.

3. Right click the query you want, and then select Properties.

4. In the Query Properties dialog box, select the Definition tab.

For more information, see Excel Services Authentication Settings.