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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
blytonpereira
Helper II
Helper II

Powerquery Keep only next N months of data

Hello

 

I have a column named Date like below. The EARLIEST DATE is dyanmic, that is sometimes it can start with any month.

 

Date (MM/DD/YYYY)

 

09/01/2018

09/01/2018

09/01/2018

10/01/2018

11/01/2018

12/01/2018

01/01/2019

02/01/2019

 

I would like to keep only the next 6 Months of dates and then i will filter out all other rows. This formulas needs to be dynamic so the NEXT N months where N=6

I am thinking it can be done with an IF statement but not sure how to write it properly so would need some help here.

 

Another option I thought could be to Group by dates and filter only the next N months, but this method is extremely slow and mostly hangs because the dataset is large. The file can be found here

https://drive.google.com/open?id=1gyPL3-BALBqMWX1Mu4KasypS46nk7zUV

 

The query is named "STAT_APR"

 

Myabe the IF statement will work better?

 

Thanks

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@blytonpereira,

 

You may add step below.

= Table.SelectRows(#"Changed Type", let earliest = List.Min(#"Changed Type"[Date]) in each [Date] < Date.AddMonths(Date.StartOfMonth(earliest), 6))
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@blytonpereira,

 

You may add step below.

= Table.SelectRows(#"Changed Type", let earliest = List.Min(#"Changed Type"[Date]) in each [Date] < Date.AddMonths(Date.StartOfMonth(earliest), 6))
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

 

Instead of the last Group by step, see if this line speeds up the process

 

#"Added Custom" = Table.AddColumn(#"Removed Columns", "First date", each List.Min(#"Removed Columns"[Date]))

 

This will give you the minimum date from the Date column.  You may now take a difference of months between the Date and this new column and filter out all those >6.

 

If this is also slow then i guess the best way would be to load all the date and in the DAX formula, we try to exclude all dates beyond 6 months of the first date.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.