handelling hindi numerics via power query

[egyblind]

hello dears

please i've data collected from online users
i want to apply some steps for data cleaning to load data faster without human review as possible
one of main problems: numbers input
some users input hindi numbers while i need all number to be arabic
example:
Hindi: ٠١٢ Arabic: 012

also which type to keep 0 as it phone number


any guide to use for data cleaning using power query?
thanks very much

 

[smozgur]

How about adding a new column and using the List.Accumulate function to replace numbers from 0 to 9?
Needs to be adapted to your source data.

let
    Source = #table(
      {"Column"},
      {
        {"٠١٢"},
        {"٠١٢١٢"},
        {"٠١٢٢٠"}
      }
    ), // Sample table
    Find = {"٠", "١", "٢"}, // This is 0, 1, and 2. Add other numbers here in Hindi
    Replace = {"0", "1", "2"}, // Add other numbers here as well

    Result = Table.AddColumn(Source, "Custom", each List.Accumulate(
                List.Numbers(0, List.Count(Find)),
                [Column],
                (A, B) => Text.Replace(A, Find{B}, Replace{B}))
            )
in
    Result