Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LP280388
Resolver II
Resolver II

Remove the Duplicate row based on a condtion in PowerQuery

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 nostatusLoc
1001AState
1002IState
1002IInternational
1004IInternational
1005AState
1006IState
1006IInternational
1008IState
1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@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"

 

 

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

5 REPLIES 5
SpartaBI
Community Champion
Community Champion

@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"

 

 

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

SpartaBI
Community Champion
Community Champion

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

 

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

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

In PowerQuery

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.