How to convert names in Whatapp message to telephone number

RonaldLieuw

New member
Joined
Sep 5, 2019
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2010
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.

DateTimeMemberMessageHourWeek dayDayMonthDay-MoArea code
9/24/19 09:48 +14 7960 853195 Feel so sorry for her9Tuesday24924-914
9/24/19 09:48 +14 7960 853195 The deadline for nominations is October. 4th9Tuesday24924-914
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. 10Tuesday24924-9???
9/24/19 10:49 +1 (888) 685-6946 <Media omitted>10Tuesday24924-91 (888)
9/24/19 12:14 James Ells//gleanerblogs.com/socialimct/?p=433212Tuesday24924-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
 

Attachments

  • WhatappChats.xlsx
    12.3 KB · Views: 7
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.
 
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.
 
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])))
 
Attachment

Attachment
 

Attachments

  • WhatappChatsSolution.xlsx
    12.4 KB · Views: 7
  • Lookup exercise.pbix
    72.2 KB · Views: 9
Back
Top