Translate to Multiple Languages with Power Query - Part 3

[feboici]

I tested the part 3, and same thing :
Thanks if you have any good idea ?

 

[feboici]

Hi took a VBA code from someone (on the web) and I did a quick fix to manage carriage return (or new line). it could help you

Function GOOGLETRANSLATE(Text As String, source_language As String, target_language As String) As String
Dim URL As String
URL = "Google Translate" & source_language & "&tl=" & target_language & "&hl=en&ie=UTF-8&q=" & CLEANTEXT(Text)
'URL = "Google Translate" & source_language & "&tl=" & target_language & "&hl=en&ie=UTF-8&q=" & Text
Dim XMLHTTPS As Object
Set XMLHTTPS = CreateObject("MSXML2.ServerXMLHTTP")
XMLHTTPS.Open "GET", URL, False
XMLHTTPS.setrequestheader "User-Agent", "Mozilla/5.0 (compatible;MSIE 6.0; WindowsNT 10.0))"
XMLHTTPS.send ""

Dim HTML As Object
Set HTML = CreateObject("HTMLFile")
With HTML
.Open
.write XMLHTTPS.responseText
.Close
End With

Dim HTMLDc As HTMLDocument
Set HTMLDc = HTML

Dim Class As Object
Set Class = HTMLDc.getElementsByClassName("result-container")(0)
If Not Class Is Nothing Then
'GOOGLETRANSLATE = Class.innerText
GOOGLETRANSLATE = SPECIALREPLACE(Class.innerText)

End If

Set Class = Nothing
Set HTML = Nothing
Set XMLHTTPS = Nothing

End Function

Function CLEANTEXT(ByVal Text As String) As String
Text = Replace(Text, "&", " <advbcommercialand> ")
Text = Replace(Text, "%", " <advbpourcent> ")
Text = Replace(Text, Chr(10), Chr(13) & Chr(10))
Text = Replace(Text, Chr(13), Chr(13) & Chr(10))
Text = Replace(Text, Chr(10) & Chr(10), Chr(10))
Text = Replace(Text, Chr(13) & Chr(10) & Chr(13) & Chr(10), Chr(13) & Chr(10))
Text = Replace(Text, Chr(13) & Chr(10), " <advb1310> ")

CLEANTEXT = Text
End Function


Function SPECIALREPLACE(ByVal Text As String) As String
Text = Replace(Text, "<advbpourcent>", "%")
Text = Replace(Text, "<advbcommercialand>", "&")
Text = Replace(Text, "<advb1310>", Chr(13) & Chr(10))
Text = Replace(Text, "<advb1310 >", Chr(13) & Chr(10))
Text = Replace(Text, "< advb1310>", Chr(13) & Chr(10))
Text = Replace(Text, "< advb1310 >", Chr(13) & Chr(10))
Text = Replace(Text, " ", " ")

SPECIALREPLACE = Text
End Function


It looks a good result :

 

[smozgur]

In the implementations I provided as sample, you need to replace the line breaks with spaces in the source without too many modification. Note that you'll get the translation as single lines.

    RemoveLineBreaks = Table.TransformColumns(Source, {"English", each Text.Replace(_, "#(lf)", " ")}),

Then use RemoveLineBreaks identifier afterwards.

 

[feboici]

Hi thx for your reply the idea is to keep the line break, but with the replace function it will be fine. Thanks