Identify offsetting entries based on multiple criteria

Ian_XYZ ,

Hey, I tried to cook something, idk if it will be faster, but I would play with Buffer.

I did merge one time, but I didn't expand any of these and accessed only individual columns. Merge itself it's really that bad if you have good sources and you don'T over do it. The performance issues will happen when using List Contains on unbuffered stuff and other scanning functions. You can theoretically play with HashedJoin as well.

https://learn.microsoft.com/en-us/powerquery-m/joinalgorithm-type

I wrote an article about different types of merges and lookups, feel free to read it:

https://www.vojtechsima.com/post/why-is-power-query-list-contains-slow-faster-lookup-alternatives

Here's the solution you may try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZHNasMwDIBfJfjcgpOsD9B6x40GWnYJORhbTc38h+NA9/aznTSEJoVuJ8my+Pgk1TX6MIx6YTTaoMoZ3jMfMtJ33ihwIT1dhbVCt5kCfzV8XjkLBeH9RWUf4zt0zAk7wohRCrTvULOpEaFSXIzTgoafvbUy9p/zHY7B9ew7xDzj9CdGHKsHc8uOjieFLDE+4SaYCc+jo7pNgALPAcUIGKqnYCjhgbF3bXASOmpUQF2C7NYg5esWQytJtPsQA3TVYXUXFcb5BClHyHa5CiKDNfAlaJpmJlP+ayGjyuNZtsuNPJWZLvw2symeT/VnlyUCNc0v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    changeTypes = Table.TransformColumnTypes(#"Promoted Headers",{{"Location", type text}, {"Product", type text}, {"Customer", type text}, {"Shipping method", type text}, {"Shipping Time", type text}, {"Value", Int64.Type}, {"Description", type text}, {"Comments", type text}}),
    
    // changeTypes is your last steps, change it to your last step

    // transformations
    addReversedValue = Table.AddColumn(changeTypes, "ValueReversed", each [Value]*-1, type number),
    addIndex = Table.AddIndexColumn(addReversedValue, "Index", 0, 1, Int64.Type),
    buffer = Table.Buffer(addIndex),
    columnNamesOrigin = {"Location", "Product", "Description", "ValueReversed"},
    
    findP001 = Table.SelectRows(buffer, each [Customer]="P001"),
    columnNamesP001 = {"Location", "Product", "Description", "Value"},

    findMatchisRows = Table.AddJoinColumn( buffer, columnNamesOrigin,  findP001, columnNamesP001, "isMatching"),
    getIndexOfCleared = List.Buffer( List.Combine( List.Transform( Table.SelectRows(findMatchisRows, each not Table.IsEmpty([isMatching]) and Text.StartsWith([Customer], "T"))[isMatching], each Table.Column(_, "Index")) ) ),
    
    addComments = Table.AddColumn( buffer, "Comments2", each if List.Contains(getIndexOfCleared, [Index]) then "Cleared" else null, type text )
in
    addComments

What I do, addReversedValue so you can later merge on it, I add an index column, Buffer it, define merging columns, (you don't need extra variables for it, but I thought it's cleaner), filter the P001 rows, then merge those rows with the criteria you need and then access those merged rows that have matching value and take the index from it and buffer it. Then add new column to the original buffered table with lookup that if it's present add 'cleared'.