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 q...
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"
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"