Forum Discussion
Syndicate_Admin
2 years agoAdministrator
Power Query delete row if value found in another column
Hi, I’m very new to power query but just can’t find a suitable solution. I need to remove a row if the same value exists in another column. I know I need a conditional statement then a filter but jus...
- 2 years ago
Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVKK1YlWMoIxjGEMEyDDEMwyBbKMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID A" = _t, #"ID B" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID A", Int64.Type}, {"ID B", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID A"}, #"Changed Type", {"ID B"}, "Changed Type", JoinKind.LeftAnti), #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Changed Type"}) in #"Removed Columns"Input:
Output:
Explanation:
You are joining back to the same table you are in and do the left anti.
Hope this helps!
wdx223_Daniel
2 years agoCommunity Champion
=Table.SelectRows(YourTable,each not List.Contains(YourTable[ID B],[ID A]))
- Syndicate_Admin2 years agoAdministrator
Thanks - I'm still getting my head around all the functions available so good to know some new ones.