Feb 01 2022 10:55 AM
Feb 01 2022 10:55 AM
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.
(the sample xls is attached)
Feb 01 2022 12:37 PM
Feb 01 2022 12:44 PMSolution
Creating PivotTable add data to data model and add calculated column
= VAR agnt = Range[Agent] RETURN CALCULATE ( CONCATENATEX ( VALUES ( Range[Name] ), Range[Name], ", " ), ALL ( Range ), Range[Agent] = agnt )
Use it in PivotTable:
Feb 01 2022 01:05 PM
Dec 30 2022 05:33 PM
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.