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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dogburalHK82
Helper III
Helper III

filter the column for certain date range

Hi, 

 

I have a table as below

dogburalHK82_0-1718693412758.png

 

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

 

dogburalHK82_1-1718693517098.png

 

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

dogburalHK82_2-1718693582103.png

 

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

 

1 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"

View solution in original post

3 REPLIES 3
WanderingBI
Resolver III
Resolver III

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"

 

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.

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"

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors