filter the column for certain date range

[dogburalHK82]

Hi, 

I have a table as below

I would like to filter out the orders made by buyer XXX but only in 2023. So it beomces like below table.

When I just filter out XXX in power query, it becomes like below

Is there any Power M, that can command to filter for certain data range??

[WanderingBI]

To add the date filter:

You can click on the small arrow symbol next to your column header for "Date", then select "Date filters", then "After..." and follow the instructions there to set your cut-off date.

This way Power Query will first apply your Buyer="XXX" filter and then add the Date>="2023/01/01" filter. You can think of it as a sequence of steps that lead to the end result of the desired table.

This will generate code similar to this:

let
  Source = #table(
    {"Data","Delivered Qnt.", "PO No.", "Buyer"},
    {
      {"2023/01/01", 25, "1", "XXX"},
      {"2021/01/01", 53, "2", "XXX"},
      {"2022/01/01", 25, "3", "YYY"},
      {"2021/01/01", 15, "4", "ZZZ"}
    }
  ),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Data", type date}, {"Delivered Qnt.", Int64.Type}, {"PO No.", Int64.Type}, {"Buyer", type text}}),
  
  // Filter by Buyer
  #"Filtered rows" = Table.SelectRows(#"Changed column type", each ([Buyer] = "XXX")),

  // Filter Date >= 01/01/2023
  #"Filtered rows 1" = Table.SelectRows(#"Filtered rows", each [Data] >= #date(2023, 1, 1))
in
  #"Filtered rows 1"

[dogburalHK82]

Thanks @WanderingBI 

I think this will just filter out specific buyers regardless of date. I dont want that..

I want to apply the BUYER filter in specific period.

[WanderingBI · Accepted solution]

I am sorry I misread your sentence.

I think the trick is to use "each not". So first write a select statement that will only keep the rows of buyer=XXX and date in 2023 and then negate it with the "not" operator.

let
  Source = #table(
    {"Date","Delivered Qnt.", "PO No.", "Buyer"},
    {
      {"2023/01/01", 25, "1", "XXX"},
      {"2021/01/01", 53, "2", "XXX"},
      {"2022/01/01", 25, "3", "YYY"},
      {"2021/01/01", 15, "4", "ZZZ"}
    }
  ),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Delivered Qnt.", Int64.Type}, {"PO No.", Int64.Type}, {"Buyer", type text}}),
  
  // Keep rows where not buyer=xxx and date in 2023
  #"Filtered rows" = Table.SelectRows(#"Changed column type", each not (([Buyer] = "XXX") and ([Date] >= #date(2023, 1, 1) and [Date] <= #date(2024, 1, 1))))
in
  #"Filtered rows"