How to separate Phone Numbers in the Name Column-CodePudding

I have a few problems that I have been stuck with for a few days now.

I have a table as below:

| Full Name       | Atlanta_Email_Only

| 16788889999     | [email protected]            
| 14045556666     | [email protected]
| John Harris     | [email protected]           
| Sarah Smith     | [email protected]

How can I use Power Query Editor to separate the Full Name into 2 columns; one is Join By Phone, and one is Full Name.

And for the email, how can I delete all the emails that does not contain the word Atlanta in it.

I have tried to use Split Column -> By Digit to Non-Digit / By Non_Digit to Digit for the Full Name, but it didn't work.

I also tried the Add Column -> Conditonal Column to drop the Email without containing the word Atlanta, but it also didn't work.

Thank you for you help.

CodePudding user response：

In powerquery ... Right click Full name column and duplicate it

Click the new column, Transform data type .. whole number

Right click new column, replace errors, null

That is the numbers

Add column .. custom column to compare the new column with the original column using formula similar to:

= if [#"Full Name - Copy"] = null then [Full Name] else null

This is the text

Right click and remove original Full Name column

To filter the emails, right click the email column, transform .. lowercase

edit the code in the code window (or in Home ... advanced ... ) from

, Text.Lower, type text}})

, each if Text.Contains(_,"atlanta") then _ else null , type text}})

Full code sample below:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Duplicated Column" = Table.DuplicateColumn(Source, "Full Name", "Join By Phone"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Join By Phone", Int64.Type}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Join By Phone", null}}),
#"Added Custom" = Table.AddColumn(#"Replaced Errors", "FullName2", each if [#"Join By Phone"] = null then [Full Name] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Full Name"}),
#"FilterEmail" = Table.TransformColumns(#"Removed Columns",{{"Atlanta_Email_Only", each if Text.Contains(_,"atlanta") then _ else null , type text}})
in #"FilterEmail"