Forum Discussion

KuntalSingh's avatar
KuntalSingh
Helper V
1 year ago

Data in two column matching

  if both the ZA and RE rows  in Document Type column should be marked as match if the Text column of the ZA document has a number that exactly matches the field for the RE invoice Reference number ...
Need Help
dufoq3's avatar
dufoq3
Community Champion
1 year ago

Hi KuntalSingh, try this:

 

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZdNb9swDIb/itHTBtQ2SZH62M1Ik85Yawd2smFfh67bYUBOG3rYv58kN43tOWnQpguCwJRh+dHLVxTz+fNZ+8qaFAFep3x2fkYAihQZFwL/Xbdk4zCFgU+F/1GYAqb3IwrDXU6BtiMiAGxFWwq3mbXRVvxliowIJrNx1ovJEWSHJjyWK8zjfF/PjyB0O8JmPkmIkoJ6IPRzOSKw3HvlMXgTZCSp9mB4D4bWGpQtGOAIjHOE8LR6UE6nYPtc6Lx+QTltyBjC9wHESSa44xqGYQ2Uq27aKSgGFENhzkXZvp03STsr59WqXJSzAaXaS+nD3Yin9KooUOaklMoL5yRoPSurVdEms7pZ1k2xKusqqTpQGec5N/nAh2M1PVqnJqMzVmnnXEwsSRbxt4kexu+bN8ubP+nN7/Tbz83mx3c/5PIDWQcgB8jT6J7iSHhvUdODR/YTO/Uf4C1LeL5oiuuieZesq3JRN9dJUV0ks6KZe8cUy6W/uJpeiHs0C6ghZkG0AjaopKsHZDPur6MfTi6DaP86WGmFrMOj83ZVtsllU6+X50lZzbLjuCW3eU9+Bdqn4KWpyU/rAvXllzsvkr5M6vVqUa5W86YN7PvQ6bDkKAqiXYjDBo2kWmdGeuTDOO57d8gniM4+mxR1/6jwpF4Co/iUpMdUknFhpu0W2SMoWmUg7mRGY411VuJmJJDM9ewwiuM2P5D+4yrHY7B6YFwQYsMnR32KqiHZo05hbFTQJOAirPXvUEagg3OZcn3YQRxhUT/iVtF7aOuEjgYeauuivCfHfYq4Ch4TF63mrWktGvYlkiMd6oxVj3YYR1oFp3btv7xD36I3pCCdjpYerMBAAar62M7qZNnUF+vZqk2urvbUrIf2Mc4waVtL3B1pvp3xPZCkZciSw95ZMAj8wbC422yS4vb2193NJpawuInpJZi7Suubct13g0cVljFzOuAcRuE7jUf+E+z6IfngD9qirC7nzZ5iRfedt9orplJaMNxxmq0BZ7qTC03mdn3NKJw4acNpPtFfPpd52wHLwKtOuh0wQE5HkOP4RaHDXxw5KDRaVl09cAa0ZcJoYWTOeAc5Cqf7Sbpn/voX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Scripts = _t, Vendor = _t, VendorName = _t, CompanyCode = _t, FiscalYear = _t, DocumentType = _t, DocumentDate = _t, PostingKey = _t, PostingDate = _t, DocumentNumber = _t, Reference = _t, Amountindoccurr = _t, Documentcurrency = _t, Amountinlocalcurrency = _t, LocalCurrency = _t, Text = _t, Netduedate = _t]),
    ZA_Text = List.Buffer(Table.SelectRows(Source, each ([DocumentType] = "ZA"))[Text]),
    RE_Reference = List.Buffer(Table.SelectRows(Source, each ([DocumentType] = "RE"))[Reference]),
    StepBack = Source,
    Ad_Check = Table.AddColumn(StepBack, "Check", each 
        if [DocumentType] = "ZA" then 
            if List.Contains(RE_Reference, [Text]) then "match" else null else 
        if List.Contains(ZA_Text, [Reference]) then "match" else null, type text)
in
    Ad_Check