Power Query Problems

Fix Power Query on Mac Requiring On-Premises Data Gateway in Excel

Rana Garcia Sep 28, 2026 868 views

Question details

The user needs to resolve an Excel for Mac Power Query error that incorrectly requests an on-premises data gateway when attempting to call an external API.

Fix Excel Power Query on Mac: On-Premises Data Gateway Is Required

Product: Microsoft Excel
Device & OS: macOS
Scenario: Using Power Query functions like Json.Document and Web.Contents to retrieve data from a web API (such as a weather API).
Observed behavior: Excel blocks the query and displays an error indicating that an on-premises data gateway is required, even though the gateway software is not supported on macOS.

Before you start

Verify that your Mac is connected to the internet and check if the API endpoint you are trying to query is publicly accessible or requires specific authentication headers.

Solution 1Recommended

Use a Supported Windows Environment

Since the Microsoft on-premises data gateway is officially unsupported on macOS, running Excel in a Windows environment is the most reliable workaround for queries that strictly require it.

Microsoft's Power Query architecture sometimes forces web queries through a gateway if it cannot verify the cloud compatibility of the data source. Because the gateway software cannot be installed on a Mac, you must emulate or connect to a Windows environment.

Set up a Windows Virtual Machine

Install a virtualization software like Parallels Desktop, VMware Fusion, or use Apple's Boot Camp (on Intel Macs) to run Windows on your macOS device.

Install Excel for Windows

Download and install the Windows version of Microsoft Excel within your newly created virtual machine.

Install the On-Premises Data Gateway

Download the standard on-premises data gateway from Microsoft's official site and install it on the Windows virtual machine.

Run Your Power Query

Open your workbook in Excel for Windows, configure your Json.Document and Web.Contents query, and authenticate through the newly installed gateway.

Cloud Alternatives: Alternatively, you can host your data queries using Power BI Service on the web or a Windows 365 Cloud PC if you prefer not to use local virtualization.

Solution 2

Adjust Query Privacy and Authentication Settings

Sometimes Power Query misinterprets a simple public Web API call as requiring a gateway due to strict privacy settings or missing credentials.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management on Mac

If you are frustrated by platform limitations like Power Query gateway errors on macOS, WPS Office offers a powerful, lightweight, and highly compatible spreadsheet alternative that natively supports Mac without requiring virtual machines.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite for Mac.
2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx files directly without any formatting loss.
3. Analyze Your Data: Use the Data tab to import web data (CSV/Text) and utilize comprehensive formula support for your data analysis.

Highly compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsNative and optimized for macOS, eliminating the need for Windows emulatorsRobust built-in data analysis, filtering, and pivot table toolsFree to download with a familiar, user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel on Mac ask for an on-premises data gateway for a simple API?

Power Query may trigger this requirement if the API is mistaken for an internal data source, if privacy settings are conflicting, or if Microsoft's cloud services cannot natively reach the endpoint. It is a known limitation when handling complex Json.Document queries on macOS.

Can I install the Microsoft on-premises data gateway on macOS?

No, Microsoft currently does not support the on-premises data gateway on macOS. It is strictly a Windows-based application, which is why Mac users must rely on virtual machines or cloud PCs to bypass this requirement.

Is there a way to call a public API on Mac without using a gateway?

Yes, for public APIs, ensure that you are calling a fully qualified external URL. Check your data source settings in Power Query to ensure no authentication or privacy conflicts are forcing Excel to treat the public API as a private intranet source.

Excel & Data Problems