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.

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.

    • icedavies's avatar
      icedavies
      Frequent Visitor
      2 years ago

      Thanks Zhengdong Xu,

       

      Apologies I'm reasonably new to Power BI.  When you say to enter that code, is that to replace the code where I've just removed the Duplicates?
      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi icedavies 

         

        No, you can add a custom column and paste the code in it:

        For example, add a custom column:

        Then paste the code in this:

         

         

        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.