How can I translate a sequesnce of strings in one column of a table using mapping from another table

[naveedulislam]

I want to replace the column (Text) in one table (corpus) using the mapping in the second table (TransliterationTable. 
The "Text" column of the corpus table and TransliterationTable are given below respspectively:

Text    
yuwladu
jamiyEu
{lna~Asi

Symbol   Arabic
}   ئ
u   ُ
w   و
l   ل
a   َ
d   د
j   ج
m   م
y   ي
E   ع
{   ٱ
n   ن
~   ّ
A   ا
s   س
i   ِ

The end results should be:

Text    
يُولَدُ
جَمِيعُ
ٱلنَّاسِ

One of the solutions, https://www.youtube.com/watch?v=MLrRlPh_ZFQ, appeared to work initially but when I applied changes, the file size grew to 15 GB and still no signs of ending iterations.
This function used the code:

(x as text) as text =>
let
    maxIterations = Table.RowCount(TranslitrationTable) ,
Iterations = List.Generate( () =>
    [Result = Text.Replace(x, TranslitrationTable[Symbol]{0}, TranslitrationTable[Arabic]{0}), Counter = 0],
        each [Counter] < maxIterations,
        each [Result = Text.Replace([Result], TranslitrationTable[Symbol]{Counter}, TranslitrationTable[Arabic]{Counter}),
                Counter = [Counter] +1], each [Result]),
                output = Iterations{maxIterations-1}
in
    output

So, I had to give up on this one. 

Due the file size (128,000 rows) the conversion is desired to be fast. I spent the whole day yesterday checking almost all similar solutions but none of them worked for me.

[ImkeF]

Your task can be performed with some simpler logic that doesn't need recursion:

let
    Source = YourTextTable,
    TextToListToTable = Table.AddColumn(Source, "Custom", each Table.FromColumns({Text.ToList([Text])})),
    MergeWithTranslations = Table.AddColumn(TextToListToTable, "Custom.1", each Table.Join([Custom],"Column1", Translations, "Symbol")),
    CombineTranslations = Table.AddColumn(MergeWithTranslations, "Result", each Text.Combine([Custom.1][Arabic], ""))
in
    CombineTranslations

[naveedulislam]

Thank you so muc. It almost worked, at least the transliteration part worked and it did work efficiently but I think thta since Arabic text is written right to left so the all the translatiterated words were shwoing in reverse order. I did some research and found a function, "Text.Reverse()". I used this as follows:

let
    Source = corpus,
    TextToTableToList = Table.AddColumn(Source, "Custom", each Table.FromColumns({Text.ToList([Text])})),
    MergeWithTranslations = Table.AddColumn(TextToTableToList, "Custom.1", each Table.Join([Custom], "Column1", Translation, "Symbol")),
    CombineTranslations = Table.AddColumn(MergeWithTranslations, "Result", each Text.Combine([Custom.1][Arabic], "")),
    ReverseText = Table.AddColumn(CombineTranslations, "ReverseOrder", each Text.Reverse([Result]))
in
    ReverseText

But it did result into unexpected results and the words got further messed up. 

I thought that perhaps reveral of the order of character needs to take place as the first  step before adding the first step. So I changed the code as below:

let
 Source = corpus,
 ReverseText = Table.AddColumn(Source, "ReverseOrder", each Text.Reverse([Text])),
 ChangedToText = Table.TransformColumnTypes(ReverseText,{{"ReverseOrder", type text}}),
 TextToTableToList = Table.AddColumn(ChangedToText, "Custom", each Table.FromColumns({Text.ToList([Text])})),
 MergeWithTranslations = Table.AddColumn(TextToTableToList, "Custom.1", each Table.Join([Custom], "Column1", Translation, "Symbol")),
 CombineTranslations = Table.AddColumn(MergeWithTranslations, "Result", each Text.Combine([Custom.1][Arabic], ""))
in
 CombineTranslations

But it still did not give the desired results. I think that since I have been looking at M code for just a few days so it is difficult to wrap my head around at least for now.
I am adding the link to the .pbix which shows the precise results in a query, "corpusByPython" in "Text" column. These are the results I obtained by transforming the same Text column using Python dataframe manupulation.

While the Result column in the Result table shows the results obtained by using M code above as provided by @ImkeF.

corpus.pbix

[ImkeF · Accepted solution]

Sorry, I didn't pay attention to that. Please try this revised code - it uses an index and sort command to achieve the desired result (hopefully 😉 😞

let
    Source = corpus,
    TextToListToTable = Table.AddColumn(Source, "Custom", each Table.AddIndexColumn(Table.FromColumns({Text.ToList([Text])}), "Sort",0,1)),
    MergeWithTranslations = Table.AddColumn(TextToListToTable, "Custom.1", each Table.Join([Custom],"Column1", Translation, "Symbol")),
    CombineTranslations = Table.AddColumn(MergeWithTranslations, "Result", each Text.Combine(Table.Sort([Custom.1], {{"Sort", Order.Descending}})[Arabic], ""))
in
    CombineTranslations

[naveedulislam]

Thank you very much @ImkeF. The solution worked after making a very minor modification.

That is  by changing "Order.Ascending" to "Order.Ascending". 

Also, I learned a lot by trying to understand the suggested M code and then trying to modify it. It actually motivated me to resume studying, "M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query." Thanks again.

Naveed

[Zubair_Muhammad]

@naveedulislam @ImkeF

Following DAX might be close as well. Basically I turn each English word into a One Column table containing individual letters in rows and lookup related Arabic letters

File attached as well

Calcuated Column =
VAR length =
    LEN ( [Text] )
VAR temp =
    ADDCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 1, length ),
            "English Letters", MID ( [Text], [Value], 1 )
        ),
        "Arabic Letters", CALCULATE (
            MIN ( Translation[Arabic] ),
            FILTER ( Translation, Translation[Symbol] = [English Letters] )
        )
    )
RETURN
    CONCATENATEX ( temp, [Arabic Letters] )