Power Query - Remove Duplicates

[joslaz]

Hello!

I'm just trying to develop a logic / approach in Excel, which cleans up my bad bookings and corrections.

The data records have the following structure (table above)following structure (table above)


If there is a number in column XX, it is a correction. This means that this line, and the line that contains the same number in YY, must be deleted.

How is this to be implemented in Power Query?

greeting

 

[VBA Geek]

You can do it like this,

let
    // load data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtT1SszTNbRQ0lEyBGIzKwMDENMEQgORhYWFUqwONoWm6AotcShENhFkHJC0NACrNSJku6GBAUSlMUGVhjhVmmKoNCTCTLCBYMpIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Day = _t, ID = _t, T1 = _t, T2 = _t, XX = _t, YY = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Day", type date}, {"ID", Int64.Type}, {"T1", type time}, {"T2", type time}, {"XX", Int64.Type}, {"YY", Int64.Type}}),
    
    // my steps
    SelfJoin = Table.NestedJoin( ChangedType, {"YY"}, ChangedType, {"XX"}, "TableJoin", JoinKind.LeftOuter),
    SelectRows = Table.SelectRows( SelfJoin, each [XX] = null and Table.IsEmpty( [TableJoin] ) ),
    RemovedColumns = Table.RemoveColumns(SelectRows,{"TableJoin"})
    
in
    RemovedColumns

 

[joslaz]

Many thanks!

I have followed your approach and this is my result:

let
Quelle = Excel.CurrentWorkbook(){[Name="Source1"]}[Content],
ZusammenführteAbfragen = Table.NestedJoin(Quelle,{"YY"},Quelle,{"XX"},"Geänderter Typ",JoinKind.LeftOuter),
ErweiterteGeänderterTyp = Table.ExpandTableColumn(ZusammenführteAbfragen, "Geänderter Typ", {"XX"}, {"XX.1"}),
GefilterteZeilen = Table.SelectRows(ErweiterteGeänderterTyp, each ([XX.1] = null)),
EntfernteSpalten = Table.RemoveColumns(GefilterteZeilen,{"XX.1"})
in
EntfernteSpalten

Do you have any suggestions, to optimize the code?
How can I adapt your approach in the code above?
Why do you have "Table.IsEmpty( [TableJoin] )"?


Best regards

 

[VBA Geek]

Hi joslaz

you have Table.IsEmpty because the the current row you are looking at must not be associated with any cancellations

The optimization of your code depends on the layout of your data, does the table you provided match the real layout?

 

[joslaz]

Thanks!

The structure to the original is near the same.

What do you think about a "JoinKind.LeftAnti)"?

 

[VBA Geek]

I guess leftanti canalso be used in your case, First you would perform a leftAnti join and then remove rows where XX is not empty