question

AbhishekGovilkar-6612 avatar image
0 Votes"
AbhishekGovilkar-6612 asked Lz-3068 commented

Filter Rows on range of data on another column in PowerBI

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:

122702-image.png

The current range of prices is as below:

122703-image.png

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

122704-image.png

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!


power-query-not-supported
image.png (34.1 KiB)
image.png (5.7 KiB)
image.png (3.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

RonRosenfeld-3452 avatar image
0 Votes"
RonRosenfeld-3452 answered RonRosenfeld-3452 edited

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

122895-image.png


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

122942-image.png



image.png (3.3 KiB)
image.png (3.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.