none
Excel Power Query Merge RRS feed

  • Question

  • I'm trying to prototype a situation for Reconciliation of Account Entries.  Basically, I have an Import of Receipt Journal Entries from one source and a second System of Record source that I'm comparing the Journal Entries to.  First, I do a straight MERGE using Left Anti Join based on Account Number, Validation Date and Amount.  Any remaining records (Outstanding Entries) I attempt to group in case the System of Record was entered with a sum amount for that day instead of individual records.  Then, I do another MERGE using Left Anti Join again based on Account Number, Validation Date and the Summed Amount for any records that were grouped.

    It works exactly as expected except for one row.  It recognizes row 1 matches (screen shot below) but doesn't recognize the second Account Number 1234 with Validation Date 12/27/2019 and Amount 23000.99.

    Does anyone see anything wrong either with my process or the data that I don't?

    Thanks.



    Sunday, February 2, 2020 1:14 AM

Answers

  • Robert

    Not easy to follow as you use terms that are different from the tables you show + it's not realistic to recreate your data and tables

    In your 1st post you were obviously fine with the output of the 1st Join/Merge + the grouping to aggregate the amounts. So the 2 blue tables below somehow represent that output. To make things easier to follow I named them according to the terms you use - hoping I did not make a mistake there...

    Highlighted in Orange are records that match in the 2 tables, and that should be excluded from the final Join/Merge according to ...show all records that exist in the Receipt Journal that are not in the System of Record. Highlighted in Green in ReceiptJournal table are records that do not match in SystemOfRecords table and that consquently should be reported. So, for me what the Merge result table reflects your final goal, no?

    I'll run the risk to assume this is correct:

        Source = Table.NestedJoin(
            ReceiptJournal, {"Account Number", "Validation Date", "Amount"},
            SystemOfRecords, {"Account Number", "Validation Date", "Deposit"},
            "Foo", JoinKind.LeftAnti
        ),
        RemovedColumns = Table.RemoveColumns(Source,{"Foo"})

    If still not good not sure one will be able to help further without you sharing a workbook with 2 tables reflecting the 2 sets of data (not thousandsss of records please :) as you receive them. Hope this makes sense

     

    Monday, February 3, 2020 3:47 PM

All replies

  • Hi Robert

    If problem still on the table, could you clarify what records you expect to get after the last Join/Merge (the one that causes your problem)?

    Asking, as in principle, this works here (but not with a LeftAnti), assuming I understood your ambition re. Reconciliation, that's where I have a doubt. What I get is the following and I would consider this as accurate but it might not be what you ultimately expect (my dec. separator is the comma)

    Monday, February 3, 2020 12:52 PM
  • Thanks for your reply.  I apologize for not explaining more thoroughly.  I have 2 input files, one a Receipt Journal that contains deposits made in a given month (usually done twice a month).  The second file is the System of Record file which I am trying to reconcile the first file with.  My goal for the end of the process is to show all records that exist in the Receipt Journal that are not in the System of Record.  Essentially reconciling like with a bank statement.  I was informed that sometimes the System of Record enters data for a single entry and sometimes the data is entered as a sum of records for a given Account Number and Date.

    First I input both files into Power Query.  Then, I merge the 2 files intending to return only records from the Receipt Journal that do not match the System of Record data.  These would be deposits that had not been recorded with the System of Record.

    My next step is to Group the Account Numbers with the same Validation Dates and sum the Amount to check if the System of Record recorded these as one deposit.

    Finally, I wanted to merge a new query with the output of the Grouped Step and the System of Record again returning only those records in the Receipt Journal that did not match records in the System of Record.  This is where I expect both 1234 Account Numbers to match and therefore be excluded from the returned result.

    Monday, February 3, 2020 1:57 PM
  • Robert

    Not easy to follow as you use terms that are different from the tables you show + it's not realistic to recreate your data and tables

    In your 1st post you were obviously fine with the output of the 1st Join/Merge + the grouping to aggregate the amounts. So the 2 blue tables below somehow represent that output. To make things easier to follow I named them according to the terms you use - hoping I did not make a mistake there...

    Highlighted in Orange are records that match in the 2 tables, and that should be excluded from the final Join/Merge according to ...show all records that exist in the Receipt Journal that are not in the System of Record. Highlighted in Green in ReceiptJournal table are records that do not match in SystemOfRecords table and that consquently should be reported. So, for me what the Merge result table reflects your final goal, no?

    I'll run the risk to assume this is correct:

        Source = Table.NestedJoin(
            ReceiptJournal, {"Account Number", "Validation Date", "Amount"},
            SystemOfRecords, {"Account Number", "Validation Date", "Deposit"},
            "Foo", JoinKind.LeftAnti
        ),
        RemovedColumns = Table.RemoveColumns(Source,{"Foo"})

    If still not good not sure one will be able to help further without you sharing a workbook with 2 tables reflecting the 2 sets of data (not thousandsss of records please :) as you receive them. Hope this makes sense

     

    Monday, February 3, 2020 3:47 PM
  • One thing to check would be to click (in the main PQ Editor, not the Merge dialog) on any cells that don't seem to be matching the way you expect. This displays their full value in a little pane at the bottom of the screen. The value shown by default in the main PQ Editor table preview is rounded, which might account for apparent discrepancies in matching.

    Ehren

    Tuesday, February 4, 2020 6:12 PM
    Owner

  •     Source = Table.NestedJoin(
            ReceiptJournal, {"Account Number", "Validation Date", "Amount"},
            SystemOfRecords, {"Account Number", "Validation Date", "Deposit"},
            "Foo", JoinKind.LeftAnti
        ),
        RemovedColumns = Table.RemoveColumns(Source,{"Foo"})
     

    Hi Robert

    How did the prototype go with the above proposal?

    Monday, February 17, 2020 9:38 AM
  • I was able to get this to work.  I'm not sure why the records weren't matching, but in walking through the proposal the part about having to group and sum records was found to be not part of the normal procedures.  Removing that step and I think there was an issue that I reconciled with the Account Number field allowed it to work.  

    The process now was something like this.  I am given a file of receipts, a file with cross-reference account numbers and a file with journal entries to reconcile.  First, I append the cross-reference account numbers to the file of receipts.  Then, I merge that appended file with the receipt journal file retaining only those rows where there is not a match on the receipt journal.  The next month, the rows that did not match the previous receipt journal export are included with the new file of receipts to reconcile with the new receipt journal export file.

    Thanks for everyone's input.

    Rob

    Tuesday, February 18, 2020 1:20 PM
  • Hi Rob

    I "think" I followed. If issue solve for now, would you mind marking the corresponding useful reply/replies (can help others)? - Thanks

    + Thanks for 1st reply to External table is not in the expected format that definitively requires some clarifications

    Nice day..

    Tuesday, February 18, 2020 2:19 PM