Split Data into a Second Stream based on a Value appearing multiple times

[ssripat3]

Hello community,

I have a specific set of data that is a response from the API. The API might respond with some values for each unique ID, some values might have the same ID but different data. Below is an example of the data.

Input

Book ID	Book Name	Book Pages
GTB123XYZ	The Great Gatsby	180
GTB123XYZ	Great Gatsby	176
GTB123XYZ	The Great Gatsby (1925 Edition)	185
TKM456ABC	To Kill a Mockingbird	281
TKM456ABC	To Kill a Mockingbird (Paperback)	281
NINET84DEF	1984	328
NINET84DEF	Nineteen Eighty-Four	328
PANDP789GHI	Pride and Prejudice	432
PANDP789GHI	Pride & Prejudice	432
LOTR000JKL	The Lord of the Rings	1178
HOBBIT111MNO	The Hobbit	310

Expected Output 1

Book ID	Book Name	Book Pages
GTB123XYZ	The Great Gatsby	180
GTB123XYZ	Great Gatsby	176
GTB123XYZ	The Great Gatsby (1925 Edition)	185
TKM456ABC	To Kill a Mockingbird	281
TKM456ABC	To Kill a Mockingbird (Paperback)	281
NINET84DEF	1984	328
NINET84DEF	Nineteen Eighty-Four	328
PANDP789GHI	Pride and Prejudice	432
PANDP789GHI	Pride & Prejudice	432

Expected Output 2

Book ID	Book Name	Book Pages
LOTR000JKL	The Lord of the Rings	1178
HOBBIT111MNO	The Hobbit	310

Now, I would like to split the data into two streams where one stream contains the non-unique Book IDs and the other stream contains unique Book IDs similar to the Expected Output 1 and 2

Can anyone please help me with this? Thank you

[alexnajm · Accepted solution]

Use a Summarize tool to get a count of the Book IDs, then use that with a Join tool against the original data to add on the Count field. Then you can filter to where Count = 1