Fabric is Generally Available. Browse Fabric Presentations. Work
towards your Fabric certification with the Cloud Skills Challenge.
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?
Tks a lot!
Go to Solution.
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!
View solution in original post
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?
Source = Folder.Files("D:\OneDrive - foldername\Data"),#"Filtered Rows" = Table.SelectRows(Source, let latest = List.Max(Source[Date modified]) in each [Date created] = latest)
Proud to be a Super User!
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...
Check out the November 2023 Power BI update to learn about new features.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.