Remove the Duplicate row based on a condtion in PowerQuery

[LP280388]

Hi Team,

I have a data as below. I need help on removing the Duplicate row where the Status is I and the 'Loc" is International. 

the catch here is there can be employees with same status and Loc but they will have only one row.  I only need to remove employees who have a duplicate record with this condition. 

In this below data I need to remove only 1002 and 1006 duplicate record and not the 1004.  Any idea on acheiving this please..?

emp no	status	Loc
1001	A	State
1002	I	State
1002	I	International
1004	I	International
1005	A	State
1006	I	State
1006	I	International
1008	I	State

[SpartaBI · Accepted solution]

@LP280388 

let
    Source = the first step/s that brings your table to Power Query
    #"Added Conditional Column" = Table.AddColumn(#"Source", "Sorting", each if [Loc] = "International" then 1 else 0),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Sorting", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"emp no"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Duplicates",{{"emp no", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Sorting"})
in
    #"Removed Columns"

Showcase Report – Contoso By SpartaBI

      

[SpartaBI]

@LP280388 in case of duplicated it will always be I on status on all the duplicates or can also be different combinations (AA / AI )?

[LP280388]

@SpartaBI Thanks for your response. Yes it will always be I and International

[SpartaBI]

@LP280388 You need it in Power Query or a calculated dax table is also ok?

[LP280388]

In PowerQuery