Forum Discussion

dogburalHK82's avatar
dogburalHK82
Helper III
2 years ago
Solved

filter the column for certain date range

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??

 

Need Help
  • WanderingBI's avatar
    WanderingBI
    2 years ago

    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"

3 Replies

  • WanderingBI's avatar
    WanderingBI
    Resolver III
    2 years ago

    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's avatar
      dogburalHK82
      Helper III
      2 years ago

      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's avatar
        WanderingBI
        Resolver III
        2 years ago

        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"