Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SriKandimalla
Helper I
Helper I

How to remove duplicates using a condition

Hello Power BI Community,

 

I am trying to figure out couple of things:

1) Either to remove the duplicate rows 

OR

2) To avoid them in my count column

I have a dataset where 1 employee can be repeated many times but the task should not be repeated. Please review the data image below.
RemoveDuplicateQuestion.JPG

So I have to achieve the count of 1 for the combination (employee, task). 
If there is a way to delete the rows in power Query editor that would be great. Or if there is a way to write DAX count by eliminating the duplicate occurrences, I can use that as well.

 

help is much appreciated, Thanks in advance! @Anonymous @Nathaniel_C  @Mariusz @Greg_Deckler 

 

 

1 ACCEPTED SOLUTION

@SriKandimalla -

Are you trying to achieve:

1.PNG

Measure = COUNTROWS(DISTINCT(TableName))

 

Can also be accomplished as:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk7MVdJRSjQ0VIrVgfOMjHDzjI3BvKxMEC8Jqg/Kg6qE8lBVEs8zMUHmmZqCeXmZyfk5qUhORQhAdSMEMLSA3BULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [employee = _t, task = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"employee", type text}, {"task", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type")
in
    #"Removed Duplicates"

with

Measure 2 = COUNTROWS('TableName (2)')

2.PNG

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

6 REPLIES 6
Nathaniel_C
Super User
Super User

@SriKandimalla ,

Go to Power Query, Home tab, Remove Rows, Remove Duplicates.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





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

Proud to be a Super User!




Hey @Nathaniel_C ,

Thanks for the response. I cannot delete the entire duplicates. I can assign the same task to multiple employees but the rule is each employee can have a particular task only once. So, I have to remove the rows where the count >1(excluding the 1st row with the duplicate, since I will lose the original row). But cannot remove all of them.

Hi @SriKandimalla ,

Go to Power Query, and try it. You can always reverse it.  Remove Duplicates, means after the first one.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





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

Proud to be a Super User!




Hey @Nathaniel_C ,

 

I just tried it, it has left my table with unique values of task.
I need the duplicates as well to be able to assign the same to task to various employees. What I need to get rid of are the ones with duplicated of (Employee & task) combined. 

@SriKandimalla -

Are you trying to achieve:

1.PNG

Measure = COUNTROWS(DISTINCT(TableName))

 

Can also be accomplished as:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk7MVdJRSjQ0VIrVgfOMjHDzjI3BvKxMEC8Jqg/Kg6qE8lBVEs8zMUHmmZqCeXmZyfk5qUhORQhAdSMEMLSA3BULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [employee = _t, task = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"employee", type text}, {"task", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type")
in
    #"Removed Duplicates"

with

Measure 2 = COUNTROWS('TableName (2)')

2.PNG

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



It solved the problem, Thank you @ChrisMendoza 

 

I have a follow-up question. What can we do if we have a unique column such as Row_ID before the employee column. How we will be distinct count then?


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors