Get correct file in the folder

[Daniel_Lima_Car]

Hi folks!

I have an API that gives me one file per day with all ticket information of the month, so, on day 1 I have only records for 1 day, and day 30 I have all tickets of the month.

I have a dashboard that I need to get the last file of the previous month and the last of the current month.

In the example, I need to filter the 02/06 file and the 31/05 file, and next month will be 01/07, 30/06 and 31/05 and so on.

If I were in a SQL environment I probably solved this by adding a rank column with PARTITION BY (MONTH, DAY) ORDER BY (MONTH, DAY DESC).

How to do this in PowerQuery?

Any ideas?

Tks a lot!

[Daniel_Lima_Car · Accepted solution]

Hi folks!

I've got what I wanted this way:

1. Orderd the data from "Date"

2. Created a column with rank (0..N)

3. Created a condicional column that check if the file date is the last of the month OR rank = 0

4. Filtered true columns 

  #"Personalização Adicionada1" = Table.AddColumn(#"Colunas Renomeadas3", "LastDayMonthOrFirstLine", each if [Date created] = Date.EndOfMonth([Date created]) or [Rank] = 0  then  100 else  0)

Tks for the helping! 

[vanessafvg]

as an example you do something like this and then the inverse with min for the other, create 2 queries  one for min and one for max and then append them?

let

Source = Folder.Files("D:\OneDrive - foldername\Data"),
#"Filtered Rows" = Table.SelectRows(Source, let latest = List.Max(Source[Date modified]) in each [Date created] = latest)

in
#"Filtered Rows"

[Daniel_Lima_Car]

Tks for aswering!

I've tried that but, my issue here is that each month I need to pick the last file, it worked for the first two months, then it fails, because it's not the first and last only... is the first and like 5 last files of old months...

Tks!