How to combine data in separate columns into a single row?

%3CLINGO-SUB%20id%3D%22lingo-sub-2793354%22%20slang%3D%22en-US%22%3EHow%20to%20combine%20data%20in%20separate%20columns%20into%20a%20single%20row%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2793354%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20this%20set%20of%205000%20data%20points.%3C%2FP%3E%3CP%3EThe%20data%20is%20from%20different%20years%20and%20are%20identified%20by%20the%20value%20in%20the%20first%20column.%20I%20am%20trying%20to%20get%20rid%20of%20the%20duplicate%20entry%20and%20combine%20the%20values%20from%20the%20separate%20years%20into%20a%20single%20row%20with%20the%20value%20in%20the%20first%20column.%3C%2FP%3E%3CP%3EWhat%20is%20the%20fastest%20way%20to%20do%20this%3F%3C%2FP%3E%3CP%3EPicture%20on%20left%20is%20what%20data%20looks%20like%2C%20photo%20on%20right%20is%20what%20I'm%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22data.PNG%22%20style%3D%22width%3A%20280px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F313424i6FE73A6C6E0B7211%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22data.PNG%22%20alt%3D%22data.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22data2.PNG%22%20style%3D%22width%3A%20236px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F313425i425513B1DE07DB58%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22data2.PNG%22%20alt%3D%22data2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2793354%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2793757%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20combine%20data%20in%20separate%20columns%20into%20a%20single%20row%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2793757%22%20slang%3D%22en-US%22%3Edo%20a%20power%20query%20on%20the%20data%20(left%20image)%3CBR%20%2F%3Emake%202%20copies%20of%20the%20query%20using%20duplicate%20not%20reference%2C%20the%20first%20duplicate%20query%20you%20keep%20the%20value%20to%20the%20left%2C%20the%202nd%20duplicate%20you%20keep%20the%20value%20to%20the%20right.%3CBR%20%2F%3E%3CBR%20%2F%3Edo%20a%20merge%20query%20matching%20the%20first%20column.%3CBR%20%2F%3E%3CBR%20%2F%3Echeers%3C%2FLINGO-BODY%3E
Occasional Visitor

I have this set of 5000 data points.

The data is from different years and are identified by the value in the first column. I am trying to get rid of the duplicate entry and combine the values from the separate years into a single row with the value in the first column.

What is the fastest way to do this?

Picture on left is what data looks like, photo on right is what I'm trying to achieve.

data.PNGdata2.PNG

1 Reply
do a power query on the data (left image)
make 2 copies of the query using duplicate not reference, the first duplicate query you keep the value to the left, the 2nd duplicate you keep the value to the right.

do a merge query matching the first column.

cheers