Forum Discussion

sbadiger3's avatar
sbadiger3
Helper I
2 years ago

How to remove rows based on a condition

Hi everyone, I have a table in power query. There is column in the table by name 'order_line_id'. This column has duplicates which ideally shouldn't. I need to remove the rows with duplicate order_li...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
smpa01's avatar
smpa01
Community Champion
2 years ago

sbadiger3 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRSlSK1QFxgTxzIDcJygXKGgG5yQguSHGKUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, validation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"ad", each Table.FirstN(Table.Sort(Table.AddIndexColumn(_, "Index", 1, 1),{{"Index", Order.Descending}}),1)}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"ad"}),
    #"Expanded ad" = Table.ExpandTableColumn(#"Removed Other Columns", "ad", {"Column1", "validation"}, {"Column1", "validation"})
in
    #"Expanded ad"