Forum Discussion

Luis_Caston's avatar
Luis_Caston
Icon for Helper III rankHelper 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 wrong.

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

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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
      Icon for Helper III rankHelper III
      2 years ago

      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 

  • Luis_Caston's avatar
    Luis_Caston
    Icon for Helper III rankHelper 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