Forum Discussion

icedavies's avatar
icedavies
Frequent Visitor
2 years ago
Solved

Removing Duplicates in Power Query based on X

Hi, I'm having a bit of trouble and hoping someone can help 🙂 I have a table with duplicates reference numbers in a column, lets call it "Ref Numbers". I also have another column which I will c...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi icedavies 

     

    In Power Query, select all the columns right-click and select the Remove Duplicates

    Then try this code:

     

    = Table.Group(#"Removed Duplicates", {"Ref Numbers"}, {{"Count", each if Table.RowCount(_) >1 then Table.SelectRows(_, each [Decider] = "RNS") else _ , type table }})

     

    Select the Decider in the  :

    The result is as follow:

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

Hi icedavies - Can you please provide with some sample data by attaching power bi file. it helps to analyse and provide help.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

icedavies's avatar
icedavies
Frequent Visitor
2 years ago

Thanks Rajendraongole1.  Please see an edited version of the sample table below.  There's a mix of reference numbers which appear once or more.  And a mix of RS or RNS.  I want to remove all duplicate reference numbers within the "Transform Data" section of Power BI and if a duplicated reference number has both RS and RNS then I would want the reference number with RS removed and the one with RNS kept.  Hope that makes sense

 

Ref NumbersDecider
008930502RNS
008930502RS
008930502RS
008930502RS
000000028RS
000770045RNS
000770045RNS
000770045RNS
999737400RS
001063583RS
001063583RS
001063583RNS
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi icedavies 

     

    Please try this:
    The sample data which you provided:

    Here I create a calculated table with the following dax:

    Table2 = 
    FILTER(
		SUMMARIZE(
			'Table',
			'Table'[Decider],
			'Table'[Ref Numbers]
		),
		IF(
			CALCULATE(
				DISTINCTCOUNT('Table'[Decider]),
				FILTER(
					ALLSELECTED('Table'),
					'Table'[Ref Numbers] = EARLIER('Table'[Ref Numbers])
				)
			) > 1,
			'Table'[Decider] = "RNS"
		) || CALCULATE(
			DISTINCTCOUNT('Table'[Decider]),
			FILTER(
				ALLSELECTED('Table'),
				'Table'[Ref Numbers] = EARLIER('Table'[Ref Numbers])
			)
		) = 1
	)

     The result is as follow:

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • icedavies's avatar
      icedavies
      Frequent Visitor
      2 years ago

      Hi Zhengdong Xu,

      Thank you so much for your reply but it's not quite what I was after.  I should have specified that there is a lot more data in my original table. I've only provided 2 columns to show what columns are needed to remove the duplicates based on the particular criteria.

      Also because of the relationships that I want the table to have with other tables in the model and how the users would interact with the dashboard, I would want the duplicates removed at Power Query level, rather than using DAX.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi icedavies 

         

        In Power Query, select all the columns right-click and select the Remove Duplicates

        Then try this code:

         

        = Table.Group(#"Removed Duplicates", {"Ref Numbers"}, {{"Count", each if Table.RowCount(_) >1 then Table.SelectRows(_, each [Decider] = "RNS") else _ , type table }})

         

        Select the Decider in the  :

        The result is as follow:

         

         

        Best Regards

        Zhengdong Xu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.