When I have a big data, the first thing is to try to not load all of them if possible so if my source has duplicate values, I will remove them but sometimes it is not so simple at it is. In Power Query Editor, I can use the “home -> remove rows -> remove duplicates”:

This function works if all columns have the same values but if it is not the case !!! For instance, I have this table:

With this data, I can remove them based on the low or high number, the first or the last date or a specific word.

Remove duplicates based on number

Click on “add column -> custom column”:

In the popup, change the name if need be and in the formula field, put:

if [argument1] = List.Max(Table.SelectRows(#"XXX", (nodup) => nodup[argument2] = [argument2])[argument1]) then "yes" else null

NOTE:

• For the low number, change “Max” by “Min”
• Replace argument by yours and XXX by the last step name. In my example:
  

Remove duplicates based on word

Click on “add column -> conditional column”:

In the popup, change the name if need be and based on if I am looking for a word in a sentence or not, fill as in the picture:

• For “status” (the column has the word, for instance “closed”):
  
  
• For “description” (the column contains the word, for instance “unix”):
  
  

NOTE:

• I can add another condition by clicking on “add clause”
• Since I don’t know how people will write “unix”, I will make the column as case-insensitive with the Text.Lower() function in the formula bar:
  
  

Remove duplicates based on date

It is the same process as “remove duplicates based on number”:

Once done, I will click on the expand icon of the “custom” column and click on “remove empty”:

By this way, I will keep and load only data with “yes” and the duplicate ones will be removed and not be loaded. You may be interested about those following articles:

• Power BI: load data based on date (month, year, etc.)
• Power BI: merge tables based on condition