Excel power query

[blind_user]

hello dears

it's my first post here, i'm visually impaired, use excel power query via screen-reader.

i plan to learn power bi , but didn't start yet.

i usually collect data from external sources filled by customers

wish i able to do these tasks to not keep do it manually :

1- convert hindi numerics to arabic numirecs

hendy: U+1632 - U+1642. sorry, form here not accept unicode characters.

into arabic range: 0-9

2- mobile and land line cleaning and  unified typing

customers sometimes write country code, or part of it, adding extra 0 front of number, but i want to handel cleaning depend on number length and its start characters after removing nun digits.

last point, i read people toke about column quality and destribution, how i access this via keyboard / screen-reader, if you could help in this.

  thanks very much, apologizing for long post.

[rubayatyasmin]

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.

1. 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.

1. 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,

1. 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

[blind_user]

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.

[rubayatyasmin]

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 👍.

[blind_user]

hello dear

Spoiler

@rubayatyasmin

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.

[rubayatyasmin · Accepted solution]

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 HindiToArabic

In this corrected function:

1. HindiToArabic is a function that takes a string hindiNumeral as an input.

2. 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.

3. 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

[blind_user]

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.