Power Query vs. Google Translation API - Part 2

smozgur

BatCoder

Builders Club Member

Joined

Feb 28, 2002

Messages

2,571

• Sep 15, 2020

• #1

smozgur submitted a new Excel article:

Power Query vs. Google Translation API - Part 2 - How to make more effective basic API calls in Power Query

In the first article of this series, Using Power Query in Excel to Translate Data Using the Google Translation API, we discussed retrieving data from a web server by using a simple API call. In the article, we used the Google Translation API endpoint.

Although it seems like a good idea to translate text in each row separately, there are some...

Click to expand...

Read more about this Excel article...

 

Last edited: Sep 15, 2020

H

hnd12000

New Member

Joined

Feb 10, 2019

Messages

22

Office Version

1. 365

Platform

1. Windows

• Oct 11, 2020

• #2

Very impressive !

 

H

hnd12000

New Member

Joined

Feb 10, 2019

Messages

22

Office Version

1. 365

Platform

1. Windows

• Oct 11, 2020

• #3

Very impressive !
I tried using to translate about 60 lines from English to Chinese.
It works, but stopped about 1/3 in the list ( I don't think that I hit the API limit

this is what it returns :

An error occurred in the ‘Translator’ query. Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
List

 

smozgur

BatCoder

Builders Club Member

Joined

Feb 28, 2002

Messages

2,571

• Oct 11, 2020

• #4

Any error is mostly because of the API limit in this code. It might be passing all text at once, so it is only one web call, however, content length is still a reason for Google to reject translation. I didn't have any problem with less than 5000 characters including spaces, but it surely has a limit.

Perhaps you have much more than this?

 

H

hnd12000

New Member

Joined

Feb 10, 2019

Messages

22

Office Version

1. 365

Platform

1. Windows

• Oct 17, 2020

• #5

is it that difficult to adapt the query to the non anonymous version of the API ?
Or to switch it to Azure. (they seem to have a similar service that offers 2M character free /month)

Pricing - Translator | Microsoft Azure

Buy Translator today and pay only for what you use. Compare pricing options to choose the best plan for your business.

azure.microsoft.com

 

H

happygreenshmoo

New Member

Joined

May 2, 2023

Messages

1

Office Version

1. 365

Platform

1. Windows

• May 2, 2023

• #6

Hello! This seems really useful! Is there a way to do this when the original data source is from the web? I am using get data--> web to connect to an excel file download hosted on a website. It looks like this "source = Excel.Workbook(Web.Contents("https://www.XXXXX.org/XXXXXX"). Please let me know! Also is there a way to add the translated column and the original column, but then maintain the other columns in the original query?