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
Hi,
I'm glad to hear that you're learning Power BI. It's a great tool for data manipulation and visualization. Your questions about Power Query are possible to handle with this tool.
- Converting Hindi to Arabic numerals:
In Power Query, create a function to replace each Hindi numeral with the corresponding Arabic numeral. Apply this function to each cell in the column with Hindi numerals.
Firstly, you need to create a function in Power Query that would perform the transformation. Here's a function that maps Hindi numerals (U+0966 - U+096F) to Arabic numerals:
(hindiNumeral as text) as text =>
let
arabicNumeral = Text.ReplaceRange(
Text.ReplaceRange(
Text.ReplaceRange(
Text.ReplaceRange(
Text.ReplaceRange(
Text.ReplaceRange(
Text.ReplaceRange(
Text.ReplaceRange(
Text.ReplaceRange(
Text.ReplaceRange(
hindiNumeral,
0, 1, "Arabic0"),
1, 1, "Arabic1"),
2, 1, "Arabic2"),
3, 1, "Arabic3"),
4, 1, "Arabic4"),
5, 1, "Arabic5"),
6, 1, "Arabic6"),
7, 1, "Arabic7"),
8, 1, "Arabic8"),
9, 1, "Arabic9")
in
arabicNumeral
Note: you need to change the Arabic0,Arabic1,... to actual Arabic numbers. Fabric community doesn't permit these numbers in message body.
You can then apply this function to each cell in the column containing the Hindi numerals. The actual function syntax might vary slightly based on your specific setup.
- Cleaning and unifying typing for mobile and landline numbers:
This might be a bit more complex, as you have to identify patterns and write conditions to clean the data accordingly. For example, you could use something like the following to remove all non-numeric characters and leading zeroes:
(number as text) as text =>
let
cleanedNumber = Text.Remove(number, {"-", " ", "(", ")"})
unifiedNumber = if Text.StartsWith(cleanedNumber, "0") then Text.Range(cleanedNumber, 1) else cleanedNumber
in
unifiedNumber
Then, you would have to add additional conditions based on the specific ways the numbers might be formatted,
- Column quality and distribution:
In the Power BI Desktop, there is a feature called Column Profiling, which provides you with information about the quality and distribution of your data.
Accessing Column Profiling data can be challenging without a screen, but it's not impossible. You'll need a screen-reader tool that can read tooltips, as these will contain information about data quality and distribution when you navigate the interface.
As far as I know, PQ doesn't have keyboard shortcuts for column distribution and profiling.
However, PQ does have some other keyboard shortcuts.
refer to this doc: https://learn.microsoft.com/en-us/power-query/keyboard-shortcuts
hello dear
thanks very much for your reply.
for converting numbers:
could i able to make this function usable to convert any number in loaded data? without need to create new helper column / custom one ..
do you think if change data type will change number for me without function?
exactly some customers type arabic numbers and i want to convert it to nun unicode numbers like we use in english 0-9.
sorry if i explained wrong or not clear.
i just want to make query for this to use it on any data i need to convert regardless which column has this issue.
actually hope power bi and PQ improve keyboard and accessibility to be usable for us, i'll write this in ideas and suggestion section.
many thanks for your support.
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