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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
onedayover
Helper II
Helper II

Remove rows in Power Query based on duplicates in one column and a specific value in another

Hi All

I have a table that has duplication in the rows with the exception of 1 column. I've tried to remove certain rows by maniplating some examples which I found but am having trouble trying to get the end result I need.

What I'm trying to do in power query is to remove the entire row if there are any duplicates in the "itemid" column and there is also a "D" in the "Status" column. Here is a before and after look of what I'm trying to do...

onedayover_0-1669337932650.png

Would anyone know if or how I can achieve this?

Thanks

onedayover

1 ACCEPTED SOLUTION
MahyarTF
Memorable Member
Memorable Member

Hi,

This is my solution based on the sample data :

1- I created the Duplicated columns for the ItemId and Status

2- Merge created columns (Copy-Itemid and Copy-Status)

MahyarTF_0-1669340183384.png

3- Sort rows Ascending on the Merged column :

MahyarTF_1-1669340233165.png

4- Select the Columns (as below) and remove the duplicated records :

MahyarTF_3-1669340623830.png

Also, you could delete the merged column

Appreciate your Kudos and please mark it as a solution if it helps you.

Mahyartf

View solution in original post

3 REPLIES 3
MahyarTF
Memorable Member
Memorable Member

Hi,

This is my solution based on the sample data :

1- I created the Duplicated columns for the ItemId and Status

2- Merge created columns (Copy-Itemid and Copy-Status)

MahyarTF_0-1669340183384.png

3- Sort rows Ascending on the Merged column :

MahyarTF_1-1669340233165.png

4- Select the Columns (as below) and remove the duplicated records :

MahyarTF_3-1669340623830.png

Also, you could delete the merged column

Appreciate your Kudos and please mark it as a solution if it helps you.

Mahyartf

Works perfectly, thank you MahyarTF, your help is much appreciated 🙂

Hi @onedayover 

Glad to help any time 

Mahyartf

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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