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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
screamout_93
Frequent Visitor

Remove duplicate row for two multiple columns and condition on the third column

Hi guys, I need help on the power query . My intention here is to remove the duplicate value for line and date . what i want is to make it so the query will only remove rows that has null value in [From] if any duplicate are found. Table below as an example:-

 

before

DateLineFrom
1/1/20237011
1/1/2023701

Null

1/1/20237011
1/2/20237011

1/2/2023

7021

1/2/2023

702Null

1/3/2023

701Null

 

what i expected the result to be

1) Row from 1/1/2023 [Line] 701 with null value on [From] is removed.

2) Row from 1/2/203 [Line] 702 with null value on [From] is removed.

3) Row from 1/3/2023 [Line]701 with null value on [From] is kept because no duplicate valuefor [Date] and [Line]. 

DateLineFrom
1/1/20237011
1/1/20237011
1/2/20237011

1/2/2023

7021

1/3/2023

701Null

 

Appreciate if anyone can help me on this. Thank you!

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

Hello, @screamout_93 

let
    Source = your_table,
    #"Grouped Rows" = Table.Group(Source, {"Date", "Line"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"rows", each _, type table [Date=datetime, Line=number, From=any]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "new_rows", each if [Count] > 1 then Table.SelectRows([rows], (x) => x[From] <> "Null") else [rows]),
    result = Table.Combine(#"Added Custom"[new_rows])
in
    result

View solution in original post

2 REPLIES 2
AlienSx
Super User
Super User

Hello, @screamout_93 

let
    Source = your_table,
    #"Grouped Rows" = Table.Group(Source, {"Date", "Line"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"rows", each _, type table [Date=datetime, Line=number, From=any]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "new_rows", each if [Count] > 1 then Table.SelectRows([rows], (x) => x[From] <> "Null") else [rows]),
    result = Table.Combine(#"Added Custom"[new_rows])
in
    result

Thank you! your a lifesaver.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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