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
wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
1 year ago

NewStep=let ref=List.Buffer(YourTable[Reference]),txt=List.Buffer(YourTable[Text]) in Table.AddColumn(YourTable,"MatchMark",each if [DocumentType]="ZA" then List.Contains(ref,[Text]) else List.Contains(txt,[Reference]))

KuntalSingh's avatar
KuntalSingh
Helper V
1 year ago

Thanks for relpy

It is not given the desired output ask is number in ZA type document have the same number in RE type document or vice versa as highlighte in black in given sample

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • KuntalSingh's avatar
      KuntalSingh
      Helper V
      1 year ago

      Solution is correct, However data is keep on loading more than 9 hours and data size reaches 22 GB so it is not feasible.

      Can you please help me  to apply excel formula in power query 

      IF(LEFT(NewColumn,2)="RE",COUNTIFS(NewColumn,SUBSTITUTE(NewColumn,"RE","ZA")),IF(LEFT(NewColumn,2)="ZA",COUNTIFS(NewColumn,SUBSTITUTE(NewColumn,"ZA","RE")),""))

      • KuntalSingh's avatar
        KuntalSingh
        Helper V
        1 year ago

        Can any one please help. Why size is increase from MB to 22 GB on last step

         

        #"Added Custom" = Table.AddColumn(step, "Custom", each let a=[Reference],
        b=[Text],
        c=Table.RowCount(Table.SelectRows(#"FinalTable",each [Reference]=a and [Text]=b and [MatchCheck]<>null))
        in if c>0 then "Match" else null)
        in
        #"Added Custom"

         

        Complete code is 

        let
        Source = Excel.Workbook(File.Contents("C:\Users\KUNTALSINGH\Box\PepsiCo - NA Process Session\ACL_Input.xlsx"), null, true),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Scripts", type text}, {"Vendor", Int64.Type}, {"VendorName", type text}, {"BusinessArea", type any}, {"CompanyCode", type text}, {"FiscalYear", Int64.Type}, {"DocumentType", type text}, {"DocumentDate", type date}, {"PostingKey", Int64.Type}, {"PostingDate", type date}, {"DocumentNumber", Int64.Type}, {"Reference", type text}, {"Amountindoccurr", type number}, {"DocumentCurrency", type text}, {"Amountinlocalcurr", type number}, {"LocalCurrency", type text}, {"ClearingDocument", Int64.Type}, {"ClearingDate", type text}, {"Text", type text}, {"NetDueDate", type text}}),
        #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([DocumentType] = "RE" or [DocumentType] = "ZA")),
        MergedTables = Table.NestedJoin(#"Filtered Rows2", {"Text"}, #"Filtered Rows2", {"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),
        step = Table.Buffer( #"Filtered Rows" ),
        #"Added Custom" = Table.AddColumn(step, "Custom", each let a=[Reference],
        b=[Text],
        c=Table.RowCount(Table.SelectRows(#"FinalTable",each [Reference]=a and [Text]=b and [MatchCheck]<>null))
        in if c>0 then "Match" else null)
        in
        #"Added Custom"