Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
(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 outputSo, I had to give up on this one.
Solved! Go to 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |