cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PereJoan_c43
Frequent Visitor

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:

batchIdentifierstartDateworkplace
Part 101/01/2021 14:30wp 1
Part 201/01/2021 14:46wp 1
Part 301/01/2021 14:51wp 1
Part 301/01/2021 15:15wp 2
Part 201/01/2021 15:32wp 2
Part 401/01/2021 17:31wp 1
Part 101/01/2021 17:51wp 2
Part 301/01/2021 18:51wp 3

 

In other to exclude the unique register.

 

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

 

Thanks in advance,

Joan

 

 

 

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @PereJoan_c43 ,

 

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:

vkellymsft_0-1635145808373.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

Hi @PereJoan_c43 ,

 

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:

vkellymsft_0-1635145808373.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

HotChilli
Super User
Super User

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?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.