Forum Discussion

screamout_93's avatar
screamout_93
Frequent Visitor
2 years ago
Solved

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!

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    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

2 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    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