Filter Rows on range of data on another column in PowerBI

Abhishek Govilkar 21 Reputation points
2021-08-12T08:24:04.11+00:00

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!

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,595 questions
{count} votes

Accepted answer
  1. Ron Rosenfeld 271 Reputation points
    2021-08-13T01:39:23.627+00:00

    Here's one way

        Filter = Table.SelectRows(#&#34;Changed Type&#34;, 
                each if [Item]=&#34;Apple&#34; then [Price] &gt;=70 and [Price]&lt;=120
                    else if [Item] = &#34;Banana&#34; then [Price] &gt;= 45 and [Price] &lt;= 55
                    else if [Item] = &#34;Cherry&#34; then [Price] &gt;= 180 and [Price] &lt;= 220 
                    else if [Item] = &#34;Grapes&#34; then [Price] &gt;= 135 and [Price]&lt;= 175
                    else [Price] &gt; 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=&#34;Filter&#34;]}[Content],
        filter = Table.TransformColumnTypes(srcFilter,{
            {&#34;Item&#34;,Text.Type},
            {&#34;Min&#34;, Int64.Type},
            {&#34;Max&#34;, Int64.Type}
        }),
    
    //Read in the data table
        Source = Excel.CurrentWorkbook(){[Name=&#34;Items&#34;]}[Content],
        #&#34;Changed Type&#34; = Table.TransformColumnTypes(Source,{
            {&#34;S.No&#34;, Int64.Type}, 
            {&#34;Item&#34;, type text}, 
            {&#34;Price&#34;, Int64.Type}}),
    
    //Combine the two tables
        comb = Table.Join(filter,&#34;Item&#34;, #&#34;Changed Type&#34;,&#34;Item&#34;),
    
    //Add column with null if Price is out of range
    //Then filter out the nulls and remove the unwanted columns
        #&#34;Added Custom&#34; = Table.AddColumn(comb, &#34;Filtered Price&#34;, 
            each if [Price] &gt;= [Min] and [Price] &lt;= [Max] then [Price] else null),
        #&#34;Filtered Rows&#34; = Table.SelectRows(#&#34;Added Custom&#34;, each ([Filtered Price] &lt;&gt; null)),
        #&#34;Removed Columns&#34; = Table.RemoveColumns(#&#34;Filtered Rows&#34;,{&#34;Min&#34;, &#34;Max&#34;, &#34;Filtered Price&#34;}),
    
    //Group by Item to get Min and Max Price
        #&#34;Grouped Rows&#34; = Table.Group(#&#34;Removed Columns&#34;, {&#34;Item&#34;}, {
                {&#34;Min&#34;, each List.Min([Price]), type nullable number}, 
                {&#34;Max&#34;, each List.Max([Price]), type nullable number}
            }),
    
    //Sort for appearance
        #&#34;Sorted Rows&#34; = Table.Sort(#&#34;Grouped Rows&#34;,{&lt;!-- --&gt;{&#34;Item&#34;, Order.Ascending}})
    
    in
        #&#34;Sorted Rows&#34;
    

    Results with Filter

    122942-image.png

    0 comments No comments

0 additional answers

Sort by: Most helpful