Forum Discussion

ryanb11's avatar
ryanb11
Helper III
5 years ago
Solved

remove some number sequences from open text column

I have a text field in a table where I need to substitute phone numbers where applicable.
 
For example the text field could have:
Call me on 08588812885
Call me on 07525812845
I need assitance please call me
Last tried in 2019
 
desired results:
 
remove some number sequences from open text column
Call me on
Call me on
I need assitance please call me
Last tried in 2019
 
Sometimes a phone number will be in the text but not always and the phone number entered will always be different. Number sequences such as years need not be removed.
The below custom column removes all numbers, but doesnt consider the length of the number, so 2019 gets remove but I want this kept, just 10 or 11 digit numbers removed, any ideas to amend the below formula?
 
= Text.Combine(List.RemoveItems(Text.ToList([ColumnName]),["0".."9"]))
Need Help
  • ImkeF's avatar
    ImkeF
    5 years ago

    Hi ryanb11 ,
    sorry, I'm very busy currently. Have adjusted the code below.

    It currently only works on a 1-column-table. Please let me know if you need it differently.

     

    let
  Source = Rels,
  #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Feedback], " ")),
  #"Added Custom1" = Table.AddColumn(
      #"Added Custom", 
      "Custom.1", 
      each Text.Combine(
          List.Select(
              [Custom], 
              (l) => [
                        ListOfCharacters = Text.ToList(l), 
                        Result = not (List.Count(ListOfCharacters) >= 10
                                    and List.AllTrue(
                                            List.Transform(ListOfCharacters, (x) => List.Contains({"0".."9"}, x))
                                    )
                    )][Result]
            ), " ")
    )
in
  #"Added Custom1"

     

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    ryanb11 Can the phone numbers be anywhere in the text? Can they have different formats like 555-555-5555 or (555)-555-5555? Do you want DAX or Power Query or either?

    • ryanb11's avatar
      ryanb11
      Helper III
      5 years ago

      Greg_Decklerthe phone numbers will always be in the format in the examples i sent, so 11 digits, but i want to account for a mistake in writing so want to try and include 1o digits if possible. the phone numbers can appear anywhere in the text, at the beginning middle or end. My power query column nearly works so may be best to amend that? but a solution any way is very welcome!