Forum Discussion
Removing Duplicates in Power Query based on X
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.
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
|
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.
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.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.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