Power Query: If x number is between x and y then "Delete" else "Keep"

[Dan80]

Hi all,

I have a list of flight numbers and I want to exclude some flight numbers if they are within a specified range.  How would I go about this in power query? 

For example, I have a large data set of individual flights and I want to link the dataset to the reference table of flights to keep/delete, for an easy example, flight numbers:

1 to 500 keep

501 to 600 delete

601 to 1000 keep

so flight number 53 would be kept but flight number 598 would be deleted.  Thanks,

dan80

[tringuyenminh92]

@Dan80 As my understand, your data in "reference table" will be changed/configured so you need dynamic solution to lookup the action for each flight number in Flight data.

So please kindly check my solution based on custom Function in Power Query

(sample.pbix file)

https://lookaside.fbsbx.com/file/sample.pbix?token=AWwziSi9Ut52Zsq1mOTefjlCiFMLHC5QRB2RUXvz_69JZePIc...

(excel file for data- Flights sheet and Reference sheet)

https://lookaside.fbsbx.com/file/Flights.xlsx?token=AWz-baAdF3L7Y5pW92Jeu8cQ6emRJEM9Oaz2WLR0sfKJCvHk...

Firstly, I create custom function in Query Editor:

(This function will lookup in your reference table and get the first row that value (current flight number) belongs to. And then select the action field(Delete/Keep)

(value) =>
let
    Source = Reference,
    #"Filtered Rows" = Table.First(Table.SelectRows(Source, each ([From] <= value) and ([To] >= value))),
    #"Action" =  Record.Field(#"Filtered Rows","Action")
in
    #"Action"

After that, in "Flight List" and "Add Column" tab, I select "Invoke Custom Function" to add new column with above function:

The rest thing is filtering column GetActionForFlight with Keep value:

If this works for you please accept it as solution and also give KUDOS.

[fboratino]

@tringuyenminh92 this solution works perfectly for my requirement, the only problem is that for the table "Reference" I have like 5k rows and for "Flight List" I have like 1.5m rows and when I try to add this to my report, clicking on the button close and apply, it keeps "Applying query changes" and never finishes, is there a way to optimize code execution? I think the problem is trying to apply the function for such a big number of rows.

[MarcelBeug]

My suggestion would be to create a table with individual deletes (or keeps, but you don't need both) from the table with ranges.

Next, join the table with flight numbers with the table with deletes, join type left anti, which will only keep flight numbers that are not in the table with deletes.

Query Deletes:

let
    Source = KeepOrDelete,
    #"Filtered Rows" = Table.SelectRows(Source, each ([KeepOrDelete] = "delete")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "DeleteNumber", each {[From]..[To]}, type {Int64.Type}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"DeleteNumber"}),
    #"Expanded DeleteNumber" = Table.ExpandListColumn(#"Removed Other Columns", "DeleteNumber")
in
    #"Expanded DeleteNumber"

Query FilteredFlights:

let
    Source = Table.NestedJoin(Flights,{"Flight Number"},Deletes,{"DeleteNumber"},"Deletes",JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Deletes"})
in
    #"Removed Columns"

[BhaveshPatel]

You can use Conditional column to achieve this. 

[Dan80]

Thanks but there are at least 20 ranges of numbers that need to be excluded plus I need the formula to be dynamic as the excluded numbers can change so a formula isn't an option. Anything else you can think of?

[BhaveshPatel]

I think you should use parameters in the Query to achieve what you are looking for. Please refer this to HERE for creating solutions using dynamic query parameters.

[Greg_Deckler]

Well, you could do this in Power Query or by using Report/Page/Visualization filters. In Power Query you would add a line like:

    #"Filtered Rows" = Table.SelectRows(#"Previous Step", each ([FlightID] < 501 and [FlightID] > 600))

If doing this after import, just turn drag "FlightID" to the chosen filtering area and use Advanced filtering to select flights < 501 AND greater than 600.

[Dan80]

Thansk heaps for your help (yet again!).  However, I was using a simple example, the table of exclusions are much longer than in the question and so won't be able to use a formula.  In excel I use an Index Match (not exact) and that works but need to replicate the logic in PQ.