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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
AmitSaini
Helper I
Helper I

Remove rows in Power Query based on multiple Condition

Hi,

 

I have a below table: -

 

AmitSaini_0-1737007517013.png

As you seen in the above table, we have some duplicate id's with different status and auto_status.

if we have any duplicate id then we consider only id who have the status= Yes and Auto_Status = Candidate for Automation only.

Taking example for 1st id - 1047452, need to keep only second row because who have the status= Yes and Auto_Status = Candidate for Automation, first row should remove.  And keep unique rows as well.

 

Need output like below : -

AmitSaini_1-1737007716400.png

Please help me to achieve this in Power query.

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

Hi @AmitSaini ,

 

Paste this example into Advanced Editor to see one way to achieve this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/NCsIwEIRfpeTcw6ZJmnosXgSxeJXSw0IihuoGbHx/W1H7g2g8LOzCNzszdc02SG3HUsZBaqmyfqvK4exnf0Yia1iTfsIOtntyayTjDAabHP01KW/BXzA4T3MhhzzT+uf/Nxb5f4d0whCQHuKigFxNPSofXoqlk+p9QP4LR8baeuOwHUjB5cJm2nvkJKwEiO9xRppryPSsaWQcXoBQIk7Y3AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, ID = _t, Status = _t, Count = _t, Auto_Status = _t]),
    groupProjectID = Table.Group(Source, {"Project", "ID"}, {{"ProjLineCount", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [Project=nullable text, ID=nullable text, Status=nullable text, Count=nullable text, Auto_Status=nullable text]}}),
    expandData = Table.ExpandTableColumn(groupProjectID, "data", {"Status", "Auto_Status"}, {"Status", "Auto_Status"}),
    filterAuto_Status = Table.SelectRows(expandData, each ([ProjLineCount] > 1 and [Auto_Status] = "Candidate for Automation") or ([ProjLineCount] = 1))
in
    filterAuto_Status

 

Summary:

groupProjectID = Group table on [Project] and [ID] adding Count and All Rows aggregated columns.

expandData = Reinstate our original table columns.

filterAuto_Status = Apply our conditional filter using the new [ProjLineCount] column to identify where multiple [ID]s exist per project.

 

Output:

BA_Pete_0-1737010662987.png

 

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

G_Kiranmayi
Regular Visitor

Hi

Try following the steps below:

 

1. Group your rows by ID column, like below:

G_Kiranmayi_0-1737028590918.png

 

2. Your output should look something, like this:

G_Kiranmayi_1-1737028657200.png

 

3. Now expand the table by selecting the columns as seen below:

G_Kiranmayi_2-1737028732890.png

 

4. Add a custom column to flag each row based on your condition and based on the newly added Count_Rows column, like this:

G_Kiranmayi_3-1737028830685.png

Here is the code: if [Count_Rows] = 2 and [Status] = "Yes" and [Auto_Status] = "Candidate for Automation" then 1 else if [Count_Rows] = 1 then 1 else 0

 

5. Filter the Flag column for value 1 to get the required rows. 

G_Kiranmayi_4-1737028969955.png

 

6. Finally remove the uneccesary columns which are Flag and Count_Rows and you should be left with your desired output, like below:

G_Kiranmayi_5-1737029061519.png

 

Hope this helps 🙂

 

 

View solution in original post

4 REPLIES 4
G_Kiranmayi
Regular Visitor

Hi

Try following the steps below:

 

1. Group your rows by ID column, like below:

G_Kiranmayi_0-1737028590918.png

 

2. Your output should look something, like this:

G_Kiranmayi_1-1737028657200.png

 

3. Now expand the table by selecting the columns as seen below:

G_Kiranmayi_2-1737028732890.png

 

4. Add a custom column to flag each row based on your condition and based on the newly added Count_Rows column, like this:

G_Kiranmayi_3-1737028830685.png

Here is the code: if [Count_Rows] = 2 and [Status] = "Yes" and [Auto_Status] = "Candidate for Automation" then 1 else if [Count_Rows] = 1 then 1 else 0

 

5. Filter the Flag column for value 1 to get the required rows. 

G_Kiranmayi_4-1737028969955.png

 

6. Finally remove the uneccesary columns which are Flag and Count_Rows and you should be left with your desired output, like below:

G_Kiranmayi_5-1737029061519.png

 

Hope this helps 🙂

 

 

Hi @G_Kiranmayi 

I appreciate the solution. This is also working fine. Thank you so much.

BA_Pete
Super User
Super User

Hi @AmitSaini ,

 

Paste this example into Advanced Editor to see one way to achieve this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/NCsIwEIRfpeTcw6ZJmnosXgSxeJXSw0IihuoGbHx/W1H7g2g8LOzCNzszdc02SG3HUsZBaqmyfqvK4exnf0Yia1iTfsIOtntyayTjDAabHP01KW/BXzA4T3MhhzzT+uf/Nxb5f4d0whCQHuKigFxNPSofXoqlk+p9QP4LR8baeuOwHUjB5cJm2nvkJKwEiO9xRppryPSsaWQcXoBQIk7Y3AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, ID = _t, Status = _t, Count = _t, Auto_Status = _t]),
    groupProjectID = Table.Group(Source, {"Project", "ID"}, {{"ProjLineCount", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [Project=nullable text, ID=nullable text, Status=nullable text, Count=nullable text, Auto_Status=nullable text]}}),
    expandData = Table.ExpandTableColumn(groupProjectID, "data", {"Status", "Auto_Status"}, {"Status", "Auto_Status"}),
    filterAuto_Status = Table.SelectRows(expandData, each ([ProjLineCount] > 1 and [Auto_Status] = "Candidate for Automation") or ([ProjLineCount] = 1))
in
    filterAuto_Status

 

Summary:

groupProjectID = Group table on [Project] and [ID] adding Count and All Rows aggregated columns.

expandData = Reinstate our original table columns.

filterAuto_Status = Apply our conditional filter using the new [ProjLineCount] column to identify where multiple [ID]s exist per project.

 

Output:

BA_Pete_0-1737010662987.png

 

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi @BA_Pete 

I appreciate the solution. It’s working perfectly. Thank you so much.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors