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
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
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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors