Load different files eliminating duplicates and keeping always the latest one

[vzbkb1]

Every month I receive a file in excel with active users.  Each file will not contain the people active this month, but it will not contain the people that left from the previous month. 

My idea was to include all the files into the same directory and load all of them as they come.  My issues is that I do not know how I can tell powerQuery to load the.  Can you help me?

[Anonymous]

Hi @vzbkb1 ,

How are your files named?
Could you give sample data (without privacy) and give the expected output?

How to provide sample data in the Power BI Forum

This will be very helpful, thanks.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[vzbkb1]

Let me provide you some examples 

File 1: Starting File

Employee Number	Full Name
1022	Santiago Garcia
1023	Luis Perez
1028	Carlos Rodriguez
1212	Ana Bel
752	Luz Dia
12	Hugo Caro
2154	Lara Cas
255	Javier Ros

File 2: A new file where the employee 255 (Javier Ros) has left, and a new employee has join (233 Clara Perez)

Employee Number	Full Name
1022	Santiago Garcia
1023	Luis Perez
1028	Carlos Rodriguez
1212	Ana Bel
752	Luz Dia
12	Hugo Caro
2154	Lara Cas
233	Clara Perez

At this moment the Output should be.  It will contain the people in file 1 and also the newones from file 2

Employee Number	Full Name
1022	Santiago Garcia
1023	Luis Perez
1028	Carlos Rodriguez
1212	Ana Bel
752	Luz Dia
12	Hugo Caro
2154	Lara Cas
255	Javier Ros
233	Clara Perez

And then we receive the file 3

Employee Number	Full Name
1022	Santiago Garcia
1023	Luis Perez
1028	Carlos Rodriguez
12	Hugo Caro
2154	Lara Cas
233	Clara Perez

Where employee 1212 and 752 have left but the resulting file should still contain all as before

Employee Number	Full Name
1022	Santiago Garcia
1023	Luis Perez
1028	Carlos Rodriguez
1212	Ana Bel
752	Luz Dia
12	Hugo Caro
2154	Lara Cas
255	Javier Ros
233	Clara Perez

In general the idea is always adding the new people coming in without losing in the report that people that have left

[Anonymous · Accepted solution]

Hi @vzbkb1 ,

1. You can put these files into a folder and use the Folder connector to connect to these files.

2. Combine & Transform Data.

3. Remove [Source.Name] column.

4. Select the [Employee Number] and [Full Name] columns --> Remove Duplicates.

result.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum