Forum Discussion
Excel power query
Hi there,
Correction for the previous code. We need to use Text. Replace not replaceRange. Here is the updated code.
let's use the Text.Replace function to replace each Hindi numeral with the corresponding Arabic numeral:
let HindiToArabic = (hindiNumeral as text) as text => let arabicNumeral = Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace( hindiNumeral, "०", "0"), "१", "1"), "२", "2"), "३", "3"), "४", "4"), "५", "5"), "६", "6"), "७", "7"), "८", "8"), "९", "9") in arabicNumeral in HindiToArabicIn this corrected function:
HindiToArabic is a function that takes a string hindiNumeral as an input.
This function contains a series of Text.Replace operations. Each operation replaces a Hindi numeral in hindiNumeral with the corresponding Arabic numeral. For instance, "०" is replaced with "0", "१" is replaced with "1", and so forth.
After all replacements have been made, the function returns the resulting string arabicNumeral, which contains the original string but with all Hindi numerals replaced by their corresponding Arabic numerals.
This function can then be used in Power Query to convert columns containing Hindi numerals to Arabic numerals. Note: this solution assumes that the Hindi numerals are in Unicode format. If they're not, this function may not work correctly.
For your last question, To apply changes on multiple column, I do not know if PQ provides text conversion like this. You can change data type at once by selecting similar data type columns. But language conversion will not be possible. You need to use replace or toText for that.
this document will be helpful.Type conversion - PowerQuery M | Microsoft Learn
Hello, blind_user
Yes, you can use this method without creating any custom column. The functions we are applying here is on a text range meaning on a specific column. In your case in Hindi numerals text range.
As this M code is written for Arabic and Hindi you need to modify the variables and specific data to make it work. For example, convert Hindi from Spanish. Then instead of Arabic you need to use Spanish numbers to replace it in Hindi.
Hope that makes sense.
Thanks.
If I answerd your question. accept it as a solution. If I helped you in any way, hit 👍.
hello dear
thanks very much for your care
sorry, as it first time for me to write PQ function, i just use its built-in features, as i can.
suppose i've these data loaded :
ID, Name, Mobile, Telephone, product, shipping, ...
what i need to do to convert any ArabicNumber in any columns not a specific one, into EnglishNumber.
ok. i understand how to update conversion map.
but how i could apply to all columns at once? if possible.
as i plan to load any new sheets in a specific folder, and automate steps as i can.
also, if you please,
just explain why we put 1 between find and replace
ex.: (8, 1, "Arabic8")
thanks very much for your support.
Hi there,
Correction for the previous code. We need to use Text. Replace not replaceRange. Here is the updated code.
let's use the Text.Replace function to replace each Hindi numeral with the corresponding Arabic numeral:
let HindiToArabic = (hindiNumeral as text) as text => let arabicNumeral = Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace( hindiNumeral, "०", "0"), "१", "1"), "२", "2"), "३", "3"), "४", "4"), "५", "5"), "६", "6"), "७", "7"), "८", "8"), "९", "9") in arabicNumeral in HindiToArabicIn this corrected function:
HindiToArabic is a function that takes a string hindiNumeral as an input.
This function contains a series of Text.Replace operations. Each operation replaces a Hindi numeral in hindiNumeral with the corresponding Arabic numeral. For instance, "०" is replaced with "0", "१" is replaced with "1", and so forth.
After all replacements have been made, the function returns the resulting string arabicNumeral, which contains the original string but with all Hindi numerals replaced by their corresponding Arabic numerals.
This function can then be used in Power Query to convert columns containing Hindi numerals to Arabic numerals. Note: this solution assumes that the Hindi numerals are in Unicode format. If they're not, this function may not work correctly.
For your last question, To apply changes on multiple column, I do not know if PQ provides text conversion like this. You can change data type at once by selecting similar data type columns. But language conversion will not be possible. You need to use replace or toText for that.
this document will be helpful.Type conversion - PowerQuery M | Microsoft Learn
dear
thanks very much for your care and support.
i'll try to find way to auto apply this function on any number in data.
but just to know for ability to reuse the code in language later; should define replace_text then find_text
Or logical: find_txt then replace_txt ?as i able to see here you wrote replace_str, find_str.
and in previous comment: replace, 1, find.
what's the different.
hope my question is clear.
thanks very much.