Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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!
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!
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"
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...
Tks!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |