Forum Discussion
Get correct file in the folder
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!
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!
3 Replies
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"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!
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!
