Pivot table two lines should be one

[LorneS]

I am working on a pivot table, the underlying database is tied to a common numeric identifier, and the data spans several years. While the numeric identifier is consistent, the text label associated to it is occasionally not, and as a result I end up with 2 lines in the pivot table instead of 1.

This is a simplified version of the underlying data. In this case agent 13 is the same person, they married in 2021 and the name changed.

The pivot table, predictably, looks like this

My object is for Agent 13 to be in one line, with the most recent label. 

In the actual database, 130,000 lines and 1,000 agents, it is not easy to manually change the label retroactively to make each agent-name combination the same. 

Or perhaps it is simple, and I have been overlooking something.

thank you

(the sample xls is attached)

[#2]

Hi @LorneS 

With Get & Transform aka Power Query. See attached sample

[#3]

Thank you. One additional question - the query that you developed, was that written with the Advanced Editor or was there a menu selection and wizard that led to that step in the query?

[#4 · Accepted solution]

@LorneS 
Creating PivotTable add data to data model and add calculated column

as

=
VAR agnt = Range[Agent]
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Range[Name] ), Range[Name], ", " ),
        ALL ( Range ),
        Range[Agent] = agnt
    )

Use it in PivotTable:

[#5]

The 2 steps following the ChangedTypes steps were written with the Advanced Editor. Not sure there's an easy way to do something similar with the User Interface only
I suggest you also consider @Sergei Baklan proposal as it might be closer/better from a business standpoint