title: Power Query: issue with removing duplicates function
body: <p>Problem when using "Remove Duplicates" function in Power Query.</p>
  
  <p>I'm running Excel 2013 with PowerQuery and PowerPivot. Multiple txt files in the same folder were loaded into data models by creating an connection. The tables looks like below.</p>
  
  <pre><code>CoCd    Doc.Id  Plant   PGroup  Purch.Doc.  Vendor
  
  7200    411647  7200    U36     4800311931  2000031503
  7020    421245  7020    D05     4800277051  2000032922
  7200    404320  1000    8       4800000000  2000032944
  7200    404321  7200    T48     4800293878  2000032944
  7010    425013  7010    R21     4800346743  2000036726
  </code></pre>
  
  <p>There are total 440k rows in total. By running a pivot table, I've identified 144k unique Doc.Ids. </p>
  
  <p>I then selected the Doc.Id (Whole Number) column and use the "Remove Duplicates" function in Power Query to remove the other duplicated rows. However, the final table only loaded 75k rows (should be 144k). I changed the data type of Doc.Ids to "text", then removed duplicates, the final table became 163k rows, which is some what correct as Doc.Ids contain "603" and "   603". Unfortunately I really need to have 144k rows in my final table.</p>
  
  <p>Why the remove duplicates function doesn't work in my case with Doc.Ids as whole Number?</p>
  
  <p>The code in Advance Editor looks like below:</p>
  
  <pre><code>#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"CreateTime", type time}, {"   TotalAmoun", Currency.Type}, {"Pst Date", type date}, {"Doc. Date", type date}, {"Due Date", type date}, {"DaysToDue", Int64.Type}, {"CreateDate", type date}, {"Cycle Time", type text}, {"Doc. Id", type text}, {"Purch.Doc.", Int64.Type}, {"Vendor", type text}, {"CoCd", Int64.Type}, {"Plant", type text}}),
  #"Removed Duplicates" = Table.Distinct(#"Changed Type1", {"Doc. Id"})
  in
  #"Removed Duplicates"
  </code></pre>
  
  <p>After some further digging, it appears that a chunk of Doc.Id were missing between "398103" and "657238" plus some random ones. An example list of missing numbers as below. Can't find any reasons why they should be missing.</p>
  
  <pre><code>"245233"
  "261404"
  ...
  ...
  "398103"
  ...
  ...
  "657238"
  </code></pre>