“Data Source.Error: The remote name could not be resolved”

The error "Data Source.Error: The remote name could not be resolved: '[hostname]'" indicates a Domain Name System (DNS) failure. Power Query was able to send a network request, but your computer could not translate the target server’s domain name into an IP address. This is not a data formatting, M-code syntax, or credential error. It means Excel cannot locate the target host on the network due to an incorrect URL, a missing VPN connection, or a local DNS caching issue.

Fast Fix

Check the URL or server name in your query’s Source step for typos, extra spaces, or incorrect path prefixes (e.g., leaving https:// inside a SQL Server address field). If connecting to an internal corporate database or intranet site, ensure your corporate VPN is connected. If your connection is active, open Command Prompt as Administrator and run ipconfig /flushdns.

---

Diagnostic Snapshot

Property	Details
Error Type	Data Source.Error / System.Net.WebException
Root Cause	DNS lookup failure (unable to map hostname to an IPv4/IPv6 address)
Common Sources	Web queries (Web.Contents), OData feeds, SharePoint sites, Cloud SQL instances
First-Line Diagnostic	Running nslookup <hostname> in Windows Command Prompt

---

Understanding the DNS Resolution Breakdown

Before Power Query can send credentials or request records from a server, your operating system must resolve the server’s human-readable name into a machine-readable IP address:

[Power Query: Web.Contents]
              │
              ▼
    "api.salesdata.com"
              │
              ▼
     [Local DNS Resolver]
              │
     ┌────────┴────────┐
     ▼                 ▼
[IP Found]       [Lookup Fails]
 192.0.2.1        CRASH: "The remote name could not be resolved"

When this lookup fails, the network connection halts immediately.

This error typically happens for one of four reasons:

1. Typo in the Hostname: A misspelled domain name (e.g., company.sharepointt.com instead of company.sharepoint.com).
2. Missing Corporate Network Route (VPN): The target database (e.g., sqldw.internal.corp) exists only on a private corporate network. If you are working remotely without an active VPN, your home internet router cannot resolve private corporate hostnames.
3. Stale Local DNS Cache: Your system’s local DNS cache contains outdated lookup data for a server whose IP address has recently changed.
4. Incorrect Protocol Formatting: Pasting https:// into connectors that expect a raw hostname (like the SQL Server or PostgreSQL connector) will cause the driver to search for a host literally named https://server.database.windows.net, which fails DNS validation.

---

Step-by-Step Diagnostic & Resolution

Step 1: Check the URL Syntax in the Source Step

Review the exact string being passed into the connection function:

1. In Excel, go to Data > Queries & Connections and double-click the failing query.
2. In the Applied Steps pane, click the Source step.
3. Look at the formula bar.
4. Identify syntax errors:
   • In SQL Connectors: Make sure the server name does not include http://, https://, or trailing slashes.
     • Incorrect: = Sql.Database("<https://sql.company.com/>", "DB")
     • Correct: = Sql.Database("sql.company.com", "DB")
   • In Web Connectors: Ensure the protocol is included and the domain is spelled correctly:
     • Incorrect: = Web.Contents("ww.example.com/data")
     • Correct: = Web.Contents("<https://www.example.com/data>")

---

Step 2: Test Name Resolution via Command Prompt

Use native Windows network tools to determine whether the issue is isolated to Excel or affects your entire computer:

1. Press Win + R, type cmd, and press Enter.
2. Type nslookup followed by the host domain from your error message: nslookup api.company.com
3. Evaluate the output:
   • Non-existent domain / Query refused: Your computer cannot reach a DNS server that recognizes this address. Check your internet connection or verify your corporate VPN status.
   • Addresses: [IP Address returned]: DNS resolution is working properly. If nslookup succeeds but Excel still throws the error, proceed to Step 3.

---

Step 3: Flush the Local DNS Cache

If the server’s IP address changed or your network connection recently switched between Wi-Fi, Ethernet, and VPN, clear your local DNS resolver cache:

1. Open Command Prompt as Administrator (press the Windows key, type cmd, right-click, and select Run as administrator).
2. Execute the following command: ipconfig /flushdns
3. Reset your computer’s network socket layer: netsh winsock reset
4. Restart your computer.
5. Reopen Excel and attempt to refresh the query.

Command Prompt (Admin)
  ├── ipconfig /flushdns      --> Clears stale domain-to-IP mappings
  └── netsh winsock reset     --> Restores network catalog adapters

---

Step 4: Verify Corporate VPN and Split-Tunnel Settings

If you are attempting to connect to an internal database or intranet SharePoint server:

• Ensure your enterprise VPN client (e.g., Cisco AnyConnect, GlobalProtect, FortiClient) is actively connected.
• If the VPN is connected but resolution still fails, your network may be experiencing a DNS split-tunneling conflict, where queries intended for the internal domain are being sent to your home internet provider.
• Test: Disconnect from the VPN, reconnect, and run nslookup yourserver.internal.corp. If it continues to fail, contact your network administrator to verify internal DNS routing for remote endpoints.

---

Step 5: Clear Stale Power Query Data Source Permissions

If an older connection string was cached incorrectly, Power Query may be referencing an invalid URL:

1. In Excel, navigate to Data > Get Data > Data Source Settings.
2. Select Global permissions.
3. Locate any entries referencing the misspelled or failing hostname.
4. Click Clear Permissions.
5. Reopen the query and re-enter the connection details cleanly.

For issues involving secure endpoint handshakes after the domain name resolves, see “Resource.Error: The request was aborted: Could not create SSL/TLS secure channel”. If you are having trouble connecting to OData endpoints, refer to “Excel cannot find the data provider” for OData Feeds. If the web refresh fails with generic download errors, see “The download has failed” when Refreshing Excel Web Data”. If Windows reports that you have no internet access despite being connected, consult “No Internet Connection” Error despite being Online in Office.