Forum Discussion

shaebert's avatar
shaebert
Helper III
4 years ago

Update Contact Record when Data Changes

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 NameLast NameJob TitleEmailCompanyDate
JohnDoeVP[email protected]Johnson1/1/22
John VP[email protected]Johnson11/1/21
JohnDoe [email protected]Johnson Inc.12/1/21
JohnDoeCEO[email protected] 2/1/22

 

Table2:

First NameLast NameJob TitleEmailCompany
JohnDoeCEO[email protected]

Johnson Inc.

 

Need Help

1 Reply

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    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