Forum Discussion

Chavas's avatar
Chavas
Helper II
5 years ago
Solved

Remove rows based on another table

Hello all. I am trying to optimize a little bit my report.   I have two main objects which are related. The main one it is called Ticket, and it has an ID and Status The  second one are tasks, wit...
Data Protection
  • amitchandak's avatar
    amitchandak
    5 years ago

    Chavas , In most of the connection, you have an advance option, you can give a query there. so add this query using new connection route

Chavas's avatar
Chavas
Helper II
5 years ago

Sorry for repositng, but it is possible that if you do it though the SQL statemet it takes ages to pull the data?

edhans's avatar
edhans
Community Champion
5 years ago

Chavas I strongly recommend you do not use the Advanced View with the SQL statement. It prevents further folding, it prevents incremental refresh, can can cause permissions to need to be elevated for a gateway connection.

 

If you must use as SQL statement, create a view on the server and connect to that. 

That said, you can do this 100% in Power Query against a SQL server and it will fold, as long as you do not use that SQL statement box.

  1. Create a duplicate of your Ticket table and call it TicketIDs
  2. Right-click on the ID column and select Drill Down. This is now a list. If it has duplicates, you should wrap the source statement with List.Distinct - so Source[ID] becomes List.Distinct(Source[ID]).
  3. Go back to the Task table.
  4. Filter on anything in the Task ID column. Just to get Power Query to generate a Table.SelectRows() statement for you. It will look something like this in the formula bar
    1. Table.SelectRows(Source, each ([Ticket ID] = "1"))
  5. You need to replace that with the following code:
    1. Table.SelectRows(Source, each (not List.Contains(TicketIDs, [Ticket ID])))

What this does is convert this to an IN statement in SQL, then does the opposite, so Not In basically. I don't have your tables in SQL Server but I tested this with the WorldWideImportersDW sample db from MS. This is the code it generated in the "View Native Query box. The server will process that and return the results.

 

Any questions ping back.