Translate columns with google translate API

[Luis_Caston]

Dear all,

I need your support please.

I've reading a lot about this topic and I've created this pbix file: Google translate 

I'm working on the M code inside but I don´t know what I'm doing wrong.

Could you give me a hand to translate columns from google?

[Luis_Caston]

Hi @Anonymous 
Thank you for your support!

I'm obtaining this error:

"Formula.Firewall: Consulta'Consulta1' (Step'Result') makes reference to another steps, where origin is not reachable directly".

Do you know what I'm doing wrong?

I add the pbix:

PowerBI_Google_Translate 

Besides I've added another option at the code:

 #"Added Custom" = Table.AddColumn(#"Tipo cambiado", 
    "Luis",
 each 
    if [PaisDestino] <> "" then
        let
            encodedPaisDestino =  Text.Replace(Uri.EscapeDataString(
                [PaisDestino]), "%20", "+"),
            apiEndpoint = 
                "https://translate.googleapis.com/translate_a/single?
                  client=gtx&sl=es&tl=en&dt=t&q=" & encodedPaisDestino,
            jsonResponse = try Json.Document(
                Text.FromBinary(Web.Contents(apiEndpoint))) otherwise null,
            translatedText = 
                if jsonResponse <> null and 
                  List.NonNullCount(jsonResponse) > 0 and 
                  List.NonNullCount(List.First(jsonResponse)) > 0 then
                    try List.First(List.First(jsonResponse)){0} 
                      otherwise null
                else
                    null
        in
            translatedText
    else
        "")
in
    #"Added Custom"

But I've obtained "NULL" values.

[Anonymous]

Hi @Luis_Caston ,

Base on your description, it seems like it return NULL values after adding custom column. Could you please check and provide the following info?

• Do you have the proper access to Google Translate API endpoint?

In addition, please update the codes as below to make troubleshooting. Hope it can help you find something...

let
    Origen = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\gestion.BI\Desktop\PaisDestino\CabeceraSalidas.csv"), null, null, 1252)}),
    #"Encabezados promovidos" = Table.PromoteHeaders(Origen, [PromoteAllScalars=true]),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"PaisDestino", type text}}),
    #"Added Custom" = Table.AddColumn(#"Tipo cambiado", "Luis", each 
        if [PaisDestino] <> "" then
            let
                encodedPaisDestino = Text.Replace(Uri.EscapeDataString([PaisDestino]), "%20", "+"),
                apiEndpoint = "https://translate.googleapis.com/translate_a/single?client=gtx&sl=es&tl=en&dt=t&q=" & encodedPaisDestino,
                apiResponse = try Text.FromBinary(Web.Contents(apiEndpoint)) otherwise "API request failed",
                jsonResponse = try Json.Document(apiResponse) otherwise null,
                translatedText = if jsonResponse <> null and List.NonNullCount(jsonResponse) > 0 and List.NonNullCount(List.First(jsonResponse)) > 0 then
                    try List.First(List.First(jsonResponse)){0} otherwise "Translation failed"
                else
                    "No translation"
            in
                translatedText
        else
            "No PaisDestino value"
    )
in
    #"Added Custom"

Best Regards

[Anonymous · Accepted solution]

Hi @Luis_Caston ,

Please update the codes for the query '' as below and check if it can return the expected result:

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

    Source = CabeceraSalidas,
    ChangeDataType = Table.TransformColumnTypes(Source,{{"PaisDestino", type text}}),
    Result = Table.AddColumn(
        ChangeDataType,
        "TranslatedColumn",
        each fnTranslate([PaisDestino]),
        type text
    )
in
    Result

In addition, you can refer the following link to get it.

Solved: Translate other languages column data into english... - Microsoft Fabric Community

You could call the Google Translate API to make the translation.  By creating a custom column and then using Web.Contents to pass the Chinese text to Google Translate, it will return the English translation.

https://cloud.google.com/translate/docs/reference/rest

---

Best Regards

[Luis_Caston]

Hi @Anonymous !!!

Just amazing!! You got it!!!
Take a look:

Thank you so much!
I've been reading a lot about it and I was a little bit confused.

I leave a link to the model to another users: Google_translate_api_example