Filter date on power query

Sofino

New Member
Joined
Oct 26, 2022
Messages
4
Office Version
  1. 2021
Platform
  1. Windows
Hello all,

please, i need to know how to filter dates on power query with condition " is before 2 months"
for exemple, we are on October 2022, i need to filter all dates that are "before september 2022"
next month November 2022, it will be " before october 2022"

thank you so much
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    dt = Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-1)),
    Result = Table.SelectRows(tbl, each [Date] < dt)
in
    Result

Book1
ABCDEF
1ItemDateItemDate
211/7/202376/28/2022
3211/14/202284/17/2022
4312/24/202298/31/2022
5412/26/2022126/7/2022
6511/28/2022165/1/2022
761/29/2023187/11/2022
876/28/2022
984/17/2022
1098/31/2022
11109/6/2022
12119/2/2022
13126/7/2022
14139/21/2022
151412/2/2022
16159/19/2022
17165/1/2022
18171/20/2023
19187/11/2022
20191/25/2023
21
Sheet5
 
Upvote 0
Solution
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    dt = Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-1)),
    Result = Table.SelectRows(tbl, each [Date] < dt)
in
    Result

Book1
ABCDEF
1ItemDateItemDate
211/7/202376/28/2022
3211/14/202284/17/2022
4312/24/202298/31/2022
5412/26/2022126/7/2022
6511/28/2022165/1/2022
761/29/2023187/11/2022
876/28/2022
984/17/2022
1098/31/2022
11109/6/2022
12119/2/2022
13126/7/2022
14139/21/2022
151412/2/2022
16159/19/2022
17165/1/2022
18171/20/2023
19187/11/2022
20191/25/2023
21
Sheet5
thank you so much my friend, it works perfectly.
if i need to add a condition, how can i do that?
for exemple;
Result = Table.SelectRows(tbl, each [Date] < dt and > 01/01/2019)

thank you so much
 
Upvote 0
Power Query:
Result = Table.SelectRows(tbl, each [Date] < dt and [Date] > #date(2019,1,1))
 
Upvote 0

Forum statistics

Threads
1,223,573
Messages
6,173,138
Members
452,501
Latest member
musallam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top