Forum Discussion
Solved
Remove rows in Power Query based on multiple logical criteria
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 mu...
rowstobedeleted= Table.Sort(Yoursource,{{Quality, Order.Ascending}})
and then you could use Table.Distinct to remove the duplicates.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.
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.
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!
- Hi, Just double click on Expanded Count step and check the box of the column which you want to see in the result.
- Hi, Just double click on Expanded Count step and check the box of the column which you want to see in the result.