Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Daniel_Lima_Car
New Member

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.

 

Daniel_Lima_Car_0-1685732796340.png

 

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!

1 ACCEPTED SOLUTION
Daniel_Lima_Car
New Member

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! 

View solution in original post

3 REPLIES 3
Daniel_Lima_Car
New Member

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
Super User
Super User

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"





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.