Only import the first date from multiple files

[ipon70]

What I am trying to do is import multple excel files that have overlapping dates, but the only date I want to keep is the first one it finds in each file.

EXAMPLE:  I have 31 files, each file contains repeating dates i.e. row 1 is 04/03/2024 row 2 is also 04/03/2024 and so on, this could go on for 5000 rows or 9000 rows, then it changes to 04/04/2024 and does that for 5000-9000 rows and then changes to 04/05/2024.  In the mean time in comes the next days file that now starts with row 1 is 04/04/2024 and row 2 is 04/04/2024.  I only want to keep 04/03/2024 from the first file and 04/04/2024 from the second, I would then keep 04/05/2024 from the third...so on and so forth.

This repeats for months of data. and there are overlapping dates in each file by 3 days. In the example below there are only two files in this import 04/04/2024 and 04/05/2024 the two yellow are the only dates I want to keep.  but the 04/03 was from the first file and the 04/04 is from the second file.

I hope that makes sense.

I have Power BI desktop Version 2.121.903.0.

[Anonymous · Accepted solution]

Hi @ipon70 

For your question, here is the method I provided:

Here's some dummy data

"Table"

"Table (2)"

"Table (3)"

First, import the data into power bi desktop, click "Transform Data" in the data loading interface to enter the power query.

Add an index column for each table. Make sure your date columns are arranged from smallest to largest, if not you can adjust them manually.

Select "Append Queries".

Generate the table below:

Filtering "Index" equal to 1.

Click on "Close & Apply" and you can also remove the index column to avoid the impact. Here is the result:

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.