extracting phone number anywhere in text

[Jason96]

I need to be able to extract a phone number anywhere in text. When i get power query to try and learn it can do it but have to create multiple columns whenever it's in a different format or of it's on different lines. how can i get it to extract no matter which line or format it is on?

example:

2/5/2024 8:32 AM: Problem: 6963 (next to kit doors) swallowed £9 on refill. Screen now reading 'hardware error'. (dummy phone number)
Action: contacted site asked them to reboot and turn refill key, issue remains sending to field
Contact: Ashely 0700 0000 000 (dummy phone number)
SIP Used: NTE-1-NGEN
Details:
2 _ 5 _ 2024 5:47 AM API Emailiface: Terminal Number reported as: 651

07000 000000 (dummy phone number)

6963 (next to kit doors) swallowed £9 on refill. Screen now reading 'hardware error'." (dummy phone number)

[Anonymous]

Hi @Jason96 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZFBawIxEIX/ysNeFKquq641npZWioeKoD25UuLuWIPZiSRZVv99oyK91GsPE4ZHMvPlvfW6QSdZHjWJjDOOu8NuHMUDvIh+jPRDYGHNVlMpkIyTPppMJw9vcFAehTHWteBqqbWpqUBWRVGcPPWS/mQMw7C0U1p3sMwtEYNNHSRZKP6+X+2PJ3tpi1paAllr7K/eQbOoyvKM494wgatyS7aVcZp7ZVggN+xl7sNapzxBukNo/Z7KC56lrTEekoNU2TsJDnR+hnKuoqCUUrGDI74ChUc7RbrI+PU2WCB1e9JnRKMoQhTdjgdQy9kCn44Kgflq2u615+/TecZv5KXSLjgb4wvDUFdvh2IwCt4iXcwwDRRa7WROAiuypWKpMb+ODYhHYy8flC7YP+xdArrA3GgesmT871FljT9ZGpvNDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","(dummy phone number)","~",Replacer.ReplaceText,{"Column1"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each let a=Table.AddIndexColumn(Table.FromList(Text.ToList([Column1])),"Index",0,1),
b=Table.AddColumn(a,"Flag",each 
let test1 =Table.SelectRows(a, (x)=>x[Index]=[Index]-2) 
in  if [Column1]="~" and Value.Is(Value.FromText(test1[Column1]{0}),type number) then 1 else 0),
c= Table.RemoveRowsWithErrors(Table.AddColumn(b,"test",each 
if [Flag]=1 then Text.Combine(Table.SelectRows(b,(x)=>x[Index]<[Index] and x[Index]>=[Index]-14)[Column1]) else null),{"Column1"}) in Text.Combine(List.Distinct( Table.SelectRows(c,each ([test]<>null))[test]),",")),
    #"Cleaned Text" = Table.TransformColumns(#"Added Custom",{{"Custom", Text.Clean, type text}})
in
    #"Cleaned Text"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jason96]

What Areas would i have to replace to get this to work. Would it be replace Column1 with the actual column name and would ignore this as this would be my own source? 

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZFBawIxEIX/ysNeFKquq641npZWioeKoD25UuLuWIPZiSRZVv99oyK91GsPE4ZHMvPlvfW6QSdZHjWJjDOOu8NuHMUDvIh+jPRDYGHNVlMpkIyTPppMJw9vcFAehTHWteBqqbWpqUBWRVGcPPWS/mQMw7C0U1p3sMwtEYNNHSRZKP6+X+2PJ3tpi1paAllr7K/eQbOoyvKM494wgatyS7aVcZp7ZVggN+xl7sNapzxBukNo/Z7KC56lrTEekoNU2TsJDnR+hnKuoqCUUrGDI74ChUc7RbrI+PU2WCB1e9JnRKMoQhTdjgdQy9kCn44Kgflq2u615+/TecZv5KXSLjgb4wvDUFdvh2IwCt4iXcwwDRRa7WROAiuypWKpMb+ODYhHYy8flC7YP+xdArrA3GgesmT871FljT9ZGpvNDw==", BinaryEncoding.Base64), Compression.Deflate))

[Anonymous]

Hi @Jason96 

Replace the source to your own, then if the column name is not 'Column1' replace the column name in the steps to your own column name

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jason96]

I've edited Column1 to my column name but just erroring. Not used advanced editor before. Is it error because my table doesn't have an index column or have i changed something i shouldn't have? 

let

    Source = Excel.CurrentWorkbook(){[Name="Remote_Engineer_Activity"]}[Content]in type table [Problem = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Problem", type text}}),

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","(dummy phone number)","~",Replacer.ReplaceText,{"Problem"}),

    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each let a=Table.AddIndexColumn(Table.FromList(Text.ToList([Problem])),"Index",0,1),

b=Table.AddColumn(a,"Flag",each

let test1 =Table.SelectRows(a, (x)=>x[Index]=[Index]-2)

in  if [Problem]="~" and Value.Is(Value.FromText(test1[Problem]{0}),type number) then 1 else 0),

c= Table.RemoveRowsWithErrors(Table.AddColumn(b,"test",each

if [Flag]=1 then Text.Combine(Table.SelectRows(b,(x)=>x[Index]<[Index] and x[Index]>=[Index]-14)[Problem]) else null),{"Problem"}) in Text.Combine(List.Distinct( Table.SelectRows(c,each ([test]<>null))[test]),",")),

    #"Cleaned Text" = Table.TransformColumns(#"Added Custom",{{"Custom", Text.Clean, type text}})

in

    #"Cleaned Text"

[Anonymous]

Hi @Jason96 

Can you provide som picture of the error? please hide your private data.

Best Regards!

Yolo Zhu

[Jason96]

[Anonymous]

Hi @Jason96 

Put the following code.

let
    Source = Excel.CurrentWorkbook(){[Name="Remote_Engineer_Activity"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Problem", type text}}),

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","(dummy phone number)","~",Replacer.ReplaceText,{"Problem"}),

    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each let a=Table.AddIndexColumn(Table.FromList(Text.ToList([Problem])),"Index",0,1),
b=Table.AddColumn(a,"Flag",each

let test1 =Table.SelectRows(a, (x)=>x[Index]=[Index]-2)

in  if [Column1]="~" and Value.Is(Value.FromText(test1[Column1]{0}),type number) then 1 else 0),

c= Table.RemoveRowsWithErrors(Table.AddColumn(b,"test",each

if [Flag]=1 then Text.Combine(Table.SelectRows(b,(x)=>x[Index]<[Index] and x[Index]>=[Index]-14)[Column1]) else null),{"Column1"}) in Text.Combine(List.Distinct( Table.SelectRows(c,each ([test]<>null))[test]),",")),
    #"Cleaned Text" = Table.TransformColumns(#"Added Custom",{{"Custom", Text.Clean, type text}})
in
    #"Cleaned Text"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jason96]

The column is now showing but has nothing in it

[Anonymous]

Hi @Jason96 

It can work in my example, is your data the same as the sample?

Best Regards!

Yolo Zhu

[Jason96]

The sample i gave is a rough idea, it does vary and the way that phone numbers are stored in format and area code. 

[Jason96 · Accepted solution]

for anyone interested i got it working with the below code

CheckForPhoneNumber = (text as text) as nullable text =>
let
// Define the minimum length of a phone number
MinPhoneNumberLength = 12,

// Filter out non-numeric characters
Digits = Text.Select(text, {"0".."9"}),

// Define UK area codes
UKAreaCodes = {"44", "440" ,"011" , "442", "441", "44244", "44113", "44114", "44115", "44116", "44117", "44118", "44121", "44131", "44141", "44151", "44161", "44191", "441582", "441642", "441157", "4413442", "441158", "4412442", "441392", "441138", "441698", "4419442", "441622", "441142", "441133", "441143", "441159", "441179", "441168", "441189", "441242", "441126", "441125", "441123", "441122", "441256", "441276", "441224", "441144", "441344", "441628", "441483", "441292", "441484", "441382", "441233", "441174", "441491", "441494", "441792", "441633", "441264", "441227", "441295", "441492", "4419445"}, // Remaining area codes truncated for brevity

// Find phone number candidates
PhoneNumberCandidates = List.Transform(List.Numbers(0, Text.Length(Digits) - MinPhoneNumberLength + 1), each Text.Range(Digits, _, MinPhoneNumberLength)),

// Filter valid phone numbers
ValidPhoneNumbers = List.Select(PhoneNumberCandidates, each List.Contains(UKAreaCodes, Text.Start(_, 3))),

// Return the last valid phone number found
PhoneNumber = if List.Count(ValidPhoneNumbers) > 0 then List.Last(ValidPhoneNumbers) else null
in
PhoneNumber,

[Greg_Deckler]

@Jason96 Maybe:

if Text.Length(Text.Select([Comments], {"0".."9"})) = 11
then 
    Text.Select([Comments], {"0".."9"})
else
    null

[Jason96]

Unfortunately this just came up with null