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
Hi @KuntalSingh ,
Please try this M code:
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]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Scripts", type text}, {"Vendor", Int64.Type}, {"VendorName", type text}, {"CompanyCode", type text}, {"FiscalYear", Int64.Type}, {"DocumentType", type text}, {"DocumentDate", type text}, {"PostingKey", Int64.Type}, {"PostingDate", type text}, {"DocumentNumber", Int64.Type}, {"Reference", type text}, {"Amountindoccurr", type number}, {"Documentcurrency", type text}, {"Amountinlocalcurrency", type number}, {"LocalCurrency", type text}, {"Text", type text}, {"Netduedate", type date}}),
MergedTables = Table.NestedJoin(#"Changed Type", {"Text"}, #"Changed Type", {"Reference"}, "NewColumn", JoinKind.FullOuter),
ExpandedTables = Table.ExpandTableColumn(MergedTables, "NewColumn", {"DocumentType", "Reference", "Text"}, {"DocumentType1", "Reference1", "Text1"}),
CustomColumn = Table.AddColumn(ExpandedTables, "MatchCheck", each if [DocumentType] = "ZA" and [DocumentType1] = "RE" and [Text] = [Reference1] then "Match"
else if [DocumentType1] = "ZA" and [DocumentType] = "RE" and [Text1] = [Reference] then "Match"
else null),
FinalTable = Table.RemoveColumns(CustomColumn,{"DocumentType1", "Reference1", "Text1"}),
#"Filtered Rows" = Table.SelectRows(FinalTable, each [Scripts] <> null),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each let a=[Scripts],
b=[Vendor],
c=Table.RowCount(Table.SelectRows(#"FinalTable",each [Scripts]=a and [Vendor]=b and [MatchCheck]<>null))
in if c>0 then "Match" else null)
in
#"Added Custom"
Just put all of it into the Advanced Editor, and the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
