Update Contact Record when Data Changes

[shaebert]

Hello, 

For our company's marketing events, we get the same person who signs up multiple times over time for different events. However the data they submit changes or is not always complete (See Table1). I'd like to create a table (See Table2) that takes the most updated information from the submissions (Table1) and provides one unique record based on their Email address. 

Table1:

First Name	Last Name	Job Title	Email	Company	Date
John	Doe	VP	jdoe@johnson.com	Johnson	1/1/22
John		VP	jdoe@johnson.com	Johnson	11/1/21
John	Doe		jdoe@johnson.com	Johnson Inc.	12/1/21
John	Doe	CEO	jdoe@johnson.com		2/1/22

Table2:

First Name	Last Name	Job Title	Email	Company
John	Doe	CEO	jdoe@johnson.com	Johnson Inc.

[tackytechtom]

Hi @shaebert,

I got to a solution in power query:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUXLJTwWSYQFAIislP9UhCyhcnJ+nl5yfCxTygvCALEN9Q30jI6VYHdJ1grUaYmpVwKtTwTMvWQ+k3QiHdmdXf6IMMIK6PBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t, #"Job Title" = _t, Email = _t, Company = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name", type text}, {"Last Name", type text}, {"Job Title", type text}, {"Email", type text}, {"Company", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"First Name", "Last Name"}, {{"MaxDate", each List.Max([Date]), type nullable date}, {"Email", each _, type table [First Name=nullable text, Last Name=nullable text, Job Title=nullable text, Email=nullable text, Company=nullable text, Date=nullable date]}, {"Job Title", each _, type table [First Name=nullable text, Last Name=nullable text, Job Title=nullable text, Email=nullable text, Company=nullable text, Date=nullable date]}, {"Company", each _, type table [First Name=nullable text, Last Name=nullable text, Job Title=nullable text, Email=nullable text, Company=nullable text, Date=nullable date]}}),
    #"Expanded Email" = Table.ExpandTableColumn(#"Grouped Rows", "Email", {"First Name", "Last Name", "Job Title", "Email", "Company", "Date"}, {"Email.First Name", "Email.Last Name", "Email.Job Title", "Email.Email", "Email.Company", "Email.Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Email", "Custom", each if [Email.Date] = [MaxDate] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MaxDate", "Email.First Name", "Email.Last Name", "Job Title", "Company", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Email.Job Title", "Job Title"}, {"Email.Email", "Email"}, {"Email.Company", "Company"}, {"Email.Date", "Date"}})
in
    #"Renamed Columns"

Hope this helps!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom