Home

Clusters of data without a certain value

%3CLINGO-SUB%20id%3D%22lingo-sub-767552%22%20slang%3D%22en-US%22%3EClusters%20of%20data%20without%20a%20certain%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-767552%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20file%20with%20couple%20hundred%20thousand%20rows%20of%20account%20clusters.%26nbsp%3B%20We%20are%20using%20this%20life%20for%20de-duplication%20of%20accounts%20pulled%20from%204%20databases.%26nbsp%3B%20Each%20cluster%20on%20average%20is%206%20rows%20of%20accounts%20each%20with%20a%20unique%20cluster%20number.%20Within%20those%20clusters%20someone%20has%20to%20choose%20one%20winner%20account%20marked%20with%20a%20W%20in%20a%20dedicated%20column%2C%26nbsp%3B%20an%20L%20for%20loser%20account%2C%20and%20D%20for%20accounts%20we%20don't%20want%20to%20merge.%26nbsp%3B%20I%20need%20to%20determine%20if%20there%20are%20clusters%20of%20accounts%20that%20do%20NOT%20have%20a%20W%20in%20the%20cluster.%26nbsp%3B%20Anyone%20have%20an%20idea%20for%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-767552%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-769090%22%20slang%3D%22en-US%22%3ERe%3A%20Clusters%20of%20data%20without%20a%20certain%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-769090%22%20slang%3D%22en-US%22%3EEasy%20in%20PowerQuery.%20Data%2C%20From%20Table.%20Filter%20the%20column%20with%20the%20W%20by%20unchecking%20the%20W.%20Delete%20all%20but%20the%20cluster%20column.%20Now%20remove%20duplicates.%3C%2FLINGO-BODY%3E
mcfaddenbruce28
New Contributor

I have a file with couple hundred thousand rows of account clusters.  We are using this life for de-duplication of accounts pulled from 4 databases.  Each cluster on average is 6 rows of accounts each with a unique cluster number. Within those clusters someone has to choose one winner account marked with a W in a dedicated column,  an L for loser account, and D for accounts we don't want to merge.  I need to determine if there are clusters of accounts that do NOT have a W in the cluster.  Anyone have an idea for this?

1 Reply
Easy in PowerQuery. Data, From Table. Filter the column with the W by unchecking the W. Delete all but the cluster column. Now remove duplicates.