How to convert names in Whatapp message to telephone number

[RonaldLieuw]

Hi, I need help with the following issue:
I am analyzing Whatapp chat messages. The source file is a .txt file with the following format:
Date, time stamp, telephone number/name: message text. See below for sample





9/24/19, 09:48 - James Elm: Feel so sorry for her



9/24/19, 10:49 - +1 (888) 685-6946: <Media omitted>



9/24/19, 10:31 - Reny Calm: I feel sorry that she



9/24/19, 09:48 - +44 7960 853195: The deadline for nominations is October. 4th








For the records with telephone numbers I managed to converted the telephone number into an area code like 1 (888) and 44.



Date
Time
Member
Message
Hour
Week day
Day
Month
Day-Mo
Area code


9/24/19
09:48
+14 7960 853195
Feel so sorry for her
9
Tuesday
24
9
24-9
14


9/24/19
09:48
+14 7960 853195
The deadline for nominations is October. 4th
9
Tuesday
24
9
24-9
14


9/24/19
10:31
Reny Calm
I feel sorry that she can’t enjoy her youth, and she feels so angry at such a young age. Children should not be watching us.
10
Tuesday
24
9
24-9
???


9/24/19
10:49
+1 (888) 685-6946
<Media omitted>
10
Tuesday
24
9
24-9
1 (888)


9/24/19
12:14
James Ells
//gleanerblogs.com/socialimct/?p=4332
12
Tuesday
24
9
24-9
???






The issue now is that some records have names instead of telephone numbers e.g James Elm and Reny Calm.
Any help with an automated mechanism to convert these names to telephone numbers is much appreciated.
The telephone numbers of the named persons are known.



Member

TelephoneNr



Reny Calm

+599 7 123-4545



James Ells

+44 7960 853195

[AliGW]

Not possible based on the sample provided - there is no link at all between phone numbers and people, so Excel or PowerQuery will not be able to work out who belongs to which number.

[RonaldLieuw]

AliGW, yes that's what I also taught. In the current data set I solved this issue in PBI by replacing the name with the number, but this is a manual operation that has to be performed with every refresh when new names are added to the data set. Thank you so much for the effort.

[RonaldLieuw]

I found a solution with the LOOKUPVALUE function in Power BI. For this to work you need a separate lookup file with two columns Names and Telephone number. See Excel and PBI file attached

I then made a new calculated column: Telnr = IF(LEFT(Chat[Member],1)="+", Chat[Member], LOOKUPVALUE(Lookup[TelephoneNr], Lookup[Member], (Chat[Member])))

[RonaldLieuw]

Attachment