Forum Discussion

aflintdepm's avatar
aflintdepm
Copper Contributor
Jun 21, 2023

Power Query for Workbook Merge

I am using Power Query to merge multiple workbooks.  Each workbook is a simple excel file, no tables, no formulas, no formatting.

 

The process I used is

1) Open a blank workbook

2) Go to Data-> Get Data -> From File -> From Folder

3) Load

The final file lives outside of the source folder, so there is no risk of looping

 

In the final output, I have random duplicates.  I have verified that each occurrence is a false duplicate- the record does not exist on multiple reports.  Meaning, if I have 10 workbooks, the final report might have a duplicate from workbook 1, another from workbook 3, and another from workbook 7, but not from every workbook.

 

Is there something I can do in my query to make sure that false duplicates are not created?

 

Thank you

9 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    And you are 100% sure none of the files contain any duplicates, not within a file, nor when the files are accumulated into one? Best show us your M code.
    • aflintdepm's avatar
      aflintdepm
      Copper Contributor

      JKPieterse 

       

      100% positive that the duplicates are not across files. The report is a list of patient referrals from an EHR from completely separate locations. The duplicates are from within a single location. I have traced each occurrence back to the source file and verified that only 1 row existed in that file.  I can't post a screenshot because the website here won't let me.

      Not sure the best way to share my M code, but happy to do so if you can provide a little direction.


      Thank you for trying to help

       

       

       

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Edit the query, click advanced editor, copy what is there and paste that here

Resources