Using Power Query in Excel to Translate Data Using the Google Translation API

[smozgur]

Many Thanks ...!!!

As soon as I have a moment, i will dive with delight ( zevk ) into your articles

You're welcome!

 

[devries]

Wow, this article made my (work)day! Thank you for this series, it provided a very neat translation solution.

I have also tried to implement the other solutions, however Part 2 and Part 3 did not work for me. Somehow, only using the line by line code for Part 1 worked wonderful for me and it solved.

Power Query vs. Google Translation API - Part 2
Translate to Multiple Languages with Power Query - Part 3​

I was wondering, if I wanted to extend the code below manually and write a second language in this code below by hand (so no automation from Part 2 and Part 3 where the output did not work well for me unfortunately and preferably no extra table): How would I need to extend the code below to include the language Turkish + Danish in two seperate columns (keeping everything as much the same as possible)?

I tried it myself, but please note I am not that skilled in M code. Your thoughts/feedback are highly appreciated!


code from Part 1:
----------
let
fnTranslate =
(original as text) as text =>
let
Source = Json.Document(
Web.Contents("https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=tr&dt=t&q=" & original)
),
Translation = Source{0}{0}{0}
in
Translation,

Source = Excel.CurrentWorkbook(){[Name="Original"]}[Content],
ChangeDataType = Table.TransformColumnTypes(Source,{{"English", type text}}),
Result = Table.AddColumn(
ChangeDataType,
"Turkish",
each fnTranslate([English]),
type text
),
#"Replaced Errors" = Table.ReplaceErrorValues(Result, {{"Turkish", null}}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Errors",{"Column1"})



in
#"Removed Columns"
--------------------

 

[smozgur]

Welcome to the MrExcel Message Board!

Wow, this article made my (work)day! Thank you for this series, it provided a very neat translation solution.

I have also tried to implement the other solutions, however Part 2 and Part 3 did not work for me. Somehow, only using the line by line code for Part 1 worked wonderful for me and it solved.

I was wondering, if I wanted to extend the code below manually and write a second language in this code below by hand (so no automation from Part 2 and Part 3 where the output did not work well for me unfortunately and preferably no extra table): How would I need to extend the code below to include the language Turkish + Danish in two seperate columns (keeping everything as much the same as possible)?

You're welcome. Glad to hear it helps.

The following code will help you, but as I also mentioned in the original source, this method is not perfect since it is translating each line individually.

Assuming Danish = da. In this scenario, the fnTranslate function takes a second argument called "code" as the language code. This way, we make the function call with "tr" for the first column, then add another column and call the function again, by passing the language code as "da".

let
    fnTranslate =
        (original as text, code as text) as text =>
            let
                Source = Json.Document(
                    Web.Contents("https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=" & code & "&dt=t&q=" & original)
                ),
                Translation = Source{0}{0}{0}
            in
                Translation,

    Source = Excel.CurrentWorkbook(){[Name="Original"]}[Content],
    ChangeDataType = Table.TransformColumnTypes(Source,{{"English", type text}}),
    ResultTr = Table.AddColumn(
        ChangeDataType,
        "Turkish",
        each fnTranslate([English], "tr"),
        type text
    ),
    ResultDa = Table.AddColumn(
        ResultTr,
        "Danish",
        each fnTranslate([English], "da"),
        type text
    )  
in
    ResultDa

The result:

English	Turkish	Danish
This is a Power Query sample.	Bu bir Power Query örneğidir.	Dette er et Power Query-eksempel.
It is translating from English to another language	İngilizceden başka bir dile çeviri yapıyor	Det er oversættelse fra engelsk til et andet sprog
It has been written in M code.	M koduyla yazılmıştır.	Det er skrevet i M-kode.