Power Query Problems

Translate Text in Excel via Public API with Power Query

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs a working Power Query example in Excel to translate text (e.g., French to English) using a public API without authentication, alongside guidance on handling JSON responses and troubleshooting 404 errors.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating bulk text translation directly within an Excel spreadsheet using M code to query external public APIs.
Observed behavior: Setting up the connection requires correctly parsing JSON API responses and diagnosing HTTP errors, such as 404 errors indicating invalid or missing endpoints.

Before you start

Check that the public translation API you plan to use supports anonymous access, and review its rate limits to avoid being blocked when processing multiple rows of text.

Solution 1Recommended

Configure the API Endpoint and Handle the JSON Response in Power Query

Verify your public API requirements and structure your Power Query M code to request and parse the translated text properly.

When using Power Query to fetch translations, formatting the URL correctly is crucial. Text containing spaces or special characters must be properly encoded, and the JSON response must be parsed correctly to extract the translation string.

Identify a Valid Endpoint

Find a reliable public translation API. Ensure the service has not been deprecated, as querying a discontinued endpoint is the most common cause of a 404 (Not Found) error.

Encode the Source Text

In your Power Query M code, use the function Uri.EscapeDataString([TextColumn]) to URL-encode your French text before appending it to the API URL.

Create the Web Request

Go to the Data tab, select Get Data > From Other Sources > Blank Query, and use Web.Contents() in the Advanced Editor to call your constructed API URL.

Parse the API Response

Once the API returns the JSON response, click 'Into Table' in the Power Query Editor. Click the expand arrows on the resulting record columns to drill down until you isolate the final English translated text.

Handling HTTP Errors: If you encounter a 404 error during setup, verify the API endpoint, URL encoding, HTTP method (GET vs POST), and required query parameters directly in a web browser to ensure the URL is valid.

Solution 2

Consult the Dedicated Power Query Community for Advanced M Code

If you require highly specific M code examples or encounter persistent connection failures, consult the specialized Microsoft Fabric community.

Free Microsoft Office alternative

Simplify Your Spreadsheets with WPS Office

Struggling with complex Power Query M code, rate limits, or API integrations in Microsoft Excel? Switch to WPS Office for a lightweight, feature-rich spreadsheet environment. WPS Office offers robust data handling tools and built-in features that often eliminate the need for complicated external API setups.

Fully compatible with Microsoft Excel (.xlsx, .xls) files and formulas.Free and lightweight alternative to resource-heavy data processing tools.Built-in translation and review tools for easier document localization.Familiar user interface requiring almost zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 404 error when querying a public API in Power Query?

A 404 (Not Found) error typically indicates that the API endpoint URL is misspelled, missing required language parameters, or the public API service itself has been moved or discontinued.

How do I handle URL encoding for text in Excel Power Query?

You should use the Uri.EscapeDataString() function in your M code. This correctly formats strings containing spaces, accents, and special characters before appending them to your public API endpoint URL.

Are there rate limits for translating text using public APIs?

Yes, most free public APIs have strict rate limits, even those supporting anonymous access. Processing thousands of Excel rows simultaneously can trigger a 429 (Too Many Requests) error, so batching requests is highly recommended.

How do I extract the translated text from a JSON response in Excel?

After Power Query connects to the API, it usually returns a JSON record. In the Power Query Editor, use the expand icon (two diverging arrows) in the column header to drill down through the JSON hierarchy until the translated text is isolated into a standard column.

Excel & Data Problems