Forum Discussion

Luis_Caston's avatar
Luis_Caston
Helper III
2 years ago
Solved

Translate columns with google translate API

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 ...
General Comment
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Luis_Caston
Helper III
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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