Forum Discussion

blind_user's avatar
blind_user
Regular Visitor
3 years ago
Solved

Excel power query

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.

 

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    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

     

6 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    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's avatar
      blind_user
      Regular Visitor
      3 years ago

      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's avatar
        rubayatyasmin
        Community Champion
        3 years ago

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