How can i filter non unique register values

[Anonymous]

Hi to everyone,

First of all thank you for helping other and hopefully me to learn how to use succesfully Power BI.

Hopefully i'm not crossposting..

I need to filter a table in order to keep the registers with non unique values.

Example:

batchIdentifier	startDate	workplace
Part 1	01/01/2021 14:30	wp 1
Part 2	01/01/2021 14:46	wp 1
Part 3	01/01/2021 14:51	wp 1
Part 3	01/01/2021 15:15	wp 2
Part 2	01/01/2021 15:32	wp 2
Part 4	01/01/2021 17:31	wp 1
Part 1	01/01/2021 17:51	wp 2
Part 3	01/01/2021 18:51	wp 3

In other to exclude the unique register.

If someone knows how to solve that i would be awesome 😄

Thanks in advance,

Joan

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Using below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKlEwVNJRMtQ31DcyMDJUMDSxMjYACpQXAMVjdaBKjNCUmJhhKDFGU2JqSECJqZWhKUSJES6LTK2MjTCUmKAoMbcyxrTIEE0JzC1GuNxiAVdirBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [batchIdentifier = _t, startDate = _t, workplace = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"batchIdentifier", type text}, {"startDate", type datetime}, {"workplace", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"batchIdentifier"}, {{"ALL", each _, type table [batchIdentifier=nullable text, startDate=nullable datetime, workplace=nullable text]}, {"Count", each Table.RowCount(_), Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] <> 1)),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Filtered Rows", "ALL", {"startDate", "workplace"}, {"ALL.startDate", "ALL.workplace"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded ALL",{"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ALL.startDate", "startDate"}, {"ALL.workplace", "workplace"}})
in
    #"Renamed Columns"

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[HotChilli]

Do you mean retain records where the count of batchidentifier is greater than 1 , so keep everything apart from the row for Part4      or is it more complex than this?