cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dan80 Regular Visitor
Regular Visitor

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

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

8 REPLIES 8
Super User
Super User

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

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.


Check-out my Back to School contest submission: Dinosaurs!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Dan80 Regular Visitor
Regular Visitor

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

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.

BhaveshPatel Super Contributor
Super Contributor

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

You can use Conditional column to achieve this. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Dan80 Regular Visitor
Regular Visitor

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

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 Super Contributor
Super Contributor

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

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
tringuyenminh92 New Contributor
New Contributor

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

@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...

 

2016-12-02_16h57_42.png2016-12-02_16h57_50.png

 

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)

 

2016-12-02_16h59_13.png2016-12-02_16h59_27.png

(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:

 

2016-12-02_17h03_32.png2016-12-02_17h03_47.png

 

The rest thing is filtering column GetActionForFlight with Keep value:

2016-12-02_17h07_11.png

 


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

fboratino Visitor
Visitor

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

@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 Super Contributor
Super Contributor

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

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"
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 3,647 guests
Please welcome our newest community members: