remove some number sequences from open text column

[ryanb11]

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"]))

[Greg_Deckler]

@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]

@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!

[Greg_Deckler]

@ImkeF @edhans got any Power Query tricks for this?

[ImkeF]

Hi @ryanb11 ,

this is a bit of  a mouthful, but it works:

let
  Source = #table(
      {"Column1"}, 
      List.Zip(
          {{
              "Call me on 08588812885", 
              "Call me on 07525812845", 
              "I need assitance please call me", 
              "Last tried in 2019"
            }}
        )
    ),
  #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Column1], " ")),
  #"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"

[ryanb11]

are stored in a column, so from your query i have removed the hard coded text with the column.

This gave me an error saying it cannot convert the values in the column to type list

@ImkeFthanks for your help so far, any ideas on this?

[ImkeF]

Hi @ryanb11 ,

not sure I understand, but you have to replace everything in the first step "Source" by a reference to your existing table.

Otherwise please paste used code or picture so I understand what's going on.

[ryanb11]

Hi @ImkeF 

I am having trouble changing the source to my source table, I am looking at what needs to be changed but I keep getting errors.

It my source table is called 'Rels' and the source column with the open text including phone numbers is 'Feedback', would you be able to write the code to include this?

Thanks,

[ImkeF · Accepted solution]

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"

[ryanb11]

@ImkeFreally need your help here would appreciate it if you can assist

[v-shex-msft]

Hi @ryanb11,

I think ImkeF 's formulas should help for your scenario, did these code works on your side now?

If they still not work, it will be helpful if you provide some dummy data you worked on. We can directly test coding formula on them. (notice: please not attach sensitive data into the sample records)

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng