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

[#6]

@Sergei Baklan 

Hi Sergei, I found this page through a google search. I'm experience a similar issue as the original poster, but I'm having some difficulty following the steps you mentioned? I know this is an old thread but I was hoping you could help?

What I'm trying to accomplish is having one single line per unique identifier, and to populate the other columns. However, because the source data has two lines for "100007", the Pivot table is displaying separate lines (for the same unique identifier). 

For instance, 100007 (row 4), should only have one line, where Aug 21 through Jan 22 = Andressa, and Feb 22 through Dec 22 = Hady.  Since the number of "duplicate" lines are in the thousands, it's not practical to fix manually (at the original data source level) line by line. I'm not sure what is the best work-around for this? Any help would be greatly appreciated! Thank you so much.

[#7]

@JS_CKO 

Perhaps you may provide small sample file to illustrate an issue. In general the logic with CONCANTENATEX() shall work, it's not clear why it doesn't work in your case.