Forum Discussion
How can i filter non unique register values
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
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,
KellyDid I answer your question? Mark my reply as a solution!
2 Replies
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?
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,
KellyDid I answer your question? Mark my reply as a solution!
