Calc on Duplicate Line

[DavidG007]

Hi, just wondering if anyone would be able to help with the following query.

The table below is a sample of the data produced by the power query.

Account Number	Department Code	Category	Keep or Remove?
84000001-001	701000	CostBased	Remove
84000001-001	701000	RevenueBased	Keep

I need to be able to determine that when I have a duplicate line, i.e. 'Account Number' and 'Department Code' are the same (I could concatenate to make the duplicate simpler to identify?), that I then only select the 'Category' that is "RevenueBased".

At the moment I am having to do this via a query in excel but it is taking over an hour to run due to the volume of cells it is calculating!

Any help would be really appreciated,

Thanks.

 

[mamady]

Hi,

you can select the two columns in Power Query and from Add Column choose Merge Columns. This will insert the concatenated column

then right-click on the new merged column and select remove duplicates

 

[sandy666]

maybe just filter Category by CostBased