SOLVED

Pivot table two lines should be one

%3CLINGO-SUB%20id%3D%22lingo-sub-3100759%22%20slang%3D%22en-US%22%3EPivot%20table%20two%20lines%20should%20be%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3100759%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20pivot%20table%2C%20the%20underlying%20database%20is%20tied%20to%20a%20common%20numeric%20identifier%2C%20and%20the%20data%20spans%20several%20years.%20While%20the%20numeric%20identifier%20is%20consistent%2C%20the%20text%20label%20associated%20to%20it%20is%20occasionally%20not%2C%20and%20as%20a%20result%20I%20end%20up%20with%202%20lines%20in%20the%20pivot%20table%20instead%20of%201.%3C%2FP%3E%3CP%3EThis%20is%20a%20simplified%20version%20of%20the%20underlying%20data.%20In%20this%20case%20agent%2013%20is%20the%20same%20person%2C%20they%20married%20in%202021%20and%20the%20name%20changed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22LorneS_0-1643741494103.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344286i282A44734B7101B8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22LorneS_0-1643741494103.png%22%20alt%3D%22LorneS_0-1643741494103.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20pivot%20table%2C%20predictably%2C%20looks%20like%20this%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22LorneS_1-1643741559090.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344294iEB6A3BC7E95188E3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22LorneS_1-1643741559090.png%22%20alt%3D%22LorneS_1-1643741559090.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20object%20is%20for%20Agent%2013%20to%20be%20in%20one%20line%2C%20with%20the%20most%20recent%20label.%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20actual%20database%2C%20130%2C000%20lines%20and%201%2C000%20agents%2C%20it%20is%20not%20easy%20to%20manually%20change%20the%20label%20retroactively%20to%20make%20each%20agent-name%20combination%20the%20same.%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20perhaps%20it%20is%20simple%2C%20and%20I%20have%20been%20overlooking%20something.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(the%20sample%20xls%20is%20attached)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3100759%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3101163%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20two%20lines%20should%20be%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3101163%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1187519%22%20target%3D%22_blank%22%3E%40LorneS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20Get%20%26amp%3B%20Transform%20aka%20Power%20Query.%20See%20attached%20sample%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3101349%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20two%20lines%20should%20be%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3101349%22%20slang%3D%22en-US%22%3EThank%20you.%20One%20additional%20question%20-%20the%20query%20that%20you%20developed%2C%20was%20that%20written%20with%20the%20Advanced%20Editor%20or%20was%20there%20a%20menu%20selection%20and%20wizard%20that%20led%20to%20that%20step%20in%20the%20query%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3101363%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20two%20lines%20should%20be%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3101363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1187519%22%20target%3D%22_blank%22%3E%40LorneS%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3ECreating%20PivotTable%20add%20data%20to%20data%20model%20and%20add%20calculated%20column%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20586px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344339i52D2C8AAC82FBCC9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eas%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%0AVAR%20agnt%20%3D%20Range%5BAgent%5D%0ARETURN%0A%20%20%20%20CALCULATE%20(%0A%20%20%20%20%20%20%20%20CONCATENATEX%20(%20VALUES%20(%20Range%5BName%5D%20)%2C%20Range%5BName%5D%2C%20%22%2C%20%22%20)%2C%0A%20%20%20%20%20%20%20%20ALL%20(%20Range%20)%2C%0A%20%20%20%20%20%20%20%20Range%5BAgent%5D%20%3D%20agnt%0A%20%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EUse%20it%20in%20PivotTable%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20705px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344340iCA18C91321F59447%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3101461%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20two%20lines%20should%20be%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3101461%22%20slang%3D%22en-US%22%3EThe%202%20steps%20following%20the%20ChangedTypes%20steps%20were%20written%20with%20the%20Advanced%20Editor.%20Not%20sure%20there's%20an%20easy%20way%20to%20do%20something%20similar%20with%20the%20User%20Interface%20only%3CBR%20%2F%3EI%20suggest%20you%20also%20consider%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20proposal%20as%20it%20might%20be%20closer%2Fbetter%20from%20a%20business%20standpoint%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

 

LorneS_0-1643741494103.png

 

The pivot table, predictably, looks like this

LorneS_1-1643741559090.png

 

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)

 

 

4 Replies

Hi @LorneS 

 

With Get & Transform aka Power Query. See attached sample

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?
best response confirmed by LorneS (Occasional Contributor)
Solution

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

image.png

as

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

Use it in PivotTable:

image.png

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