Multiple Entries for sam Date should use the newest Date

[Anonymous]

Hello everyone,

I have a problem. I added a folder as source and I have several files I need to use but the problem is they are created on a different date. So I have two date fields, one with the date the file was created and the other with the actual information we need.

Due to the fact that all the files are from different days, they include the other days that come after the day the file is created.

For example:

I have a file that was created on 02/19/2024, so in that file there is data for February 28th.

I have another file that was created on 02/28/2024, so in this file the first date is February 28.

Now I want that it takes only the date of the latest file created for which it is possible. Like the 19th of February can not exist in the file of the 28th of February because it is behind this date.

So how to look if there are multiple entries for the same date and everything else except some specific columns? And if there are multiple entries for the same date with a different file creation date, it should use the most recent possible.

Information Date	Date File is created	Value
02.28.2024	02.19.2024	0
02.28.2024	02.28.2024	7
02.28.2024	02.27.2024	0
02.28.2024	02.20.2024	7

So here is a little example for this case it should use the value 7 because its the newer file.

Thanks in advance.

Regards.

Steallight

[dufoq3]

Hi @Anonymous, are you sure that your sample data does make sense? I guess there are two files (1st file for first pair of rows and 2nd file for second pair of rows), but in first pair you have created value 7 for file with higher Date File but in second pair it is vice versa.

[Anonymous]

Hi @dufoq3 , i load the complete Folder. In there are 11 Files they all have the same schema. i get the date created for each File by the Folder import. So i just want to get the value for the newest date created like in this example it should only show the second row. Because it is the newest information for that day. 

[dufoq3]

@Anonymous, you can filter newest files this way:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw0je01DcyMDJRitWBCxlZIIScoELmmEIGUKFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [File = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}, "en-US"),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"File"}, {{"Newest", each Table.FirstN(_, 1), type table}}),
    CombinedTablesNewest = Table.Combine(#"Grouped Rows"[Newest])
in
    CombinedTablesNewest

[Anonymous]

The problem is that the first entry of the File from 02.28.2024 is the 02.28.2024 and so on for the rest of the files. I need the past days too and thats why it should get the newest entry in date created for the actual Date column. @dufoq3 

[Anonymous · Accepted solution]

Hi @Anonymous 

Here is my solution. You can have a try!

First bring all data into Power Query by combining the files. In the combined query,  group by "information date" column and add two new columns as below:

All Data: All Rows

Latest Date Created: Max of Date Created

You will get a result like below. Expand All Data column. 

Then filter rows where [Date Created] = [Latest Date Created]. You will get the latest value for each information date. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!