Filter Rows on range of data on another column in PowerBI

Question

I have a data table that I would like to filter based on the data range in another column.

A sample of my dataset is as below:

The current range of prices is as below:

I want to drop all rows that are outside the price range as given below:

I tried a multiple IF, such as :

Filtre = if((Table[Item]= "Apple" and [Price] >= 70 and [Price] <= 120), if(Table[Item]= "Banana" and [Price] >= 45and [Price] <= 55), if (Table[Item]= "Cherry" and [Price] >= 180 and [Price] <= 220), if (Table[Item]= "Grapes" and [Price] >= 135 and [Price] <= 175), 1 ,0)

It doesn't retrieve errors, but it doesn't filter correctly. Any help on the same will be highly appreciated.

Thanks in advance!

Answer 1

Here's one way

    Filter = Table.SelectRows(#"Changed Type", 
            each if [Item]="Apple" then [Price] >=70 and [Price]<=120
                else if [Item] = "Banana" then [Price] >= 45 and [Price] <= 55
                else if [Item] = "Cherry" then [Price] >= 180 and [Price] <= 220 
                else if [Item] = "Grapes" then [Price] >= 135 and [Price]<= 175
                else [Price] > 0),

If you wanted to have the Filter in a worksheet table, instead of hard-coding it in your Query, you could create a table like:

Filter Table

and use a version of code like this:

• join the filter table with the data table
• Create a Custom Column which writes a null if the Price is out of range
• Then filter out the nulls

let

//Read in the Filter table
    srcFilter = Excel.CurrentWorkbook(){[Name="Filter"]}[Content],
    filter = Table.TransformColumnTypes(srcFilter,{
        {"Item",Text.Type},
        {"Min", Int64.Type},
        {"Max", Int64.Type}
    }),

//Read in the data table
    Source = Excel.CurrentWorkbook(){[Name="Items"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"S.No", Int64.Type}, 
        {"Item", type text}, 
        {"Price", Int64.Type}}),

//Combine the two tables
    comb = Table.Join(filter,"Item", #"Changed Type","Item"),

//Add column with null if Price is out of range
//Then filter out the nulls and remove the unwanted columns
    #"Added Custom" = Table.AddColumn(comb, "Filtered Price", 
        each if [Price] >= [Min] and [Price] <= [Max] then [Price] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Filtered Price] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Min", "Max", "Filtered Price"}),

//Group by Item to get Min and Max Price
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Item"}, {
            {"Min", each List.Min([Price]), type nullable number}, 
            {"Max", each List.Max([Price]), type nullable number}
        }),

//Sort for appearance
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{<!-- -->{"Item", Order.Ascending}})

in
    #"Sorted Rows"

Results with Filter