Remove rows in Power Query based on multiple logical criteria

[Mythicos]

Hi,

I have a table with 3 columns:

[ID]: Whole Number

[Informations]: Text

[Info quality]: Whole Number, from 1 to 4

I want to keep one row for each distinct [ID], but the row I keep must be the one with the lowest number in the [Info quality] column.

So for example if I have:

ID	Informations	Info Quality
235	Blue	3
425	Blue	3
425	Red	2
838	White	4
235	Red	2

... I want to remove row #1 (ID = 235; Info Quality = 3) instead of row #5 (ID = 235; Info Quality = 2) and remove row #2 (ID = 425; Info Quality = 3) instead of row #3 (ID = 425; Info Quality = 2), ending up with the following:

ID	Informations	Info Quality
425	Red	2
838	White	4
235	Red	2

Thanks!

[SachinNandanwar · Accepted solution]

rowstobedeleted= Table.Sort(Yoursource,{{Quality, Order.Ascending}})

and then you could use Table.Distinct to remove the duplicates.

[HotChilli]

Correct. It can't be relied on.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Informations", type text}, {"Info Quality", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each Table.Min(_,"Info Quality")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Informations", "Info Quality"}, {"Informations", "Info Quality"})
in
    #"Expanded Count"

Hope this helps.

[Mythicos]

Hi,

Your code is based off my example, which was a simplified version of my real situation because I wanted to not overburden my question.

My real table has 16 columns, and I see in your code:

 #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Informations", "Info Quality"}, {"Informations", "Info Quality"})

... that you refer to the columns other than "ID". Do I need to insert all 15 column names twice? Or is there a short hand for doing this?

Thank!

[Ashish_Mathur]

Hi, Just double click on Expanded Count step and check the box of the column which you want to see in the result.

[Ashish_Mathur]

Hi, Just double click on Expanded Count step and check the box of the column which you want to see in the result.

[HotChilli]

Just throwing in a warning about relying on a sort order and using Table.Distinct.  The documentation has some warnings against this.

[Mythicos]

Can you elaborate a bit? You think in my scenario, there's a risk the lowest value in the sorted column won't be the one preserved post Table.Disctinct?

[SachinNandanwar · Accepted solution]

rowstobedeleted= Table.Sort(Yoursource,{{Quality, Order.Ascending}})

and then you could use Table.Distinct to remove the duplicates.