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

Hi Greg_Deckler . Thanks for your answer.

 

I have been looking for the solution but I should be doing something wrong because the RELATED function is not being accepted by the New Column editor.

 

I have a table such as:

TICKET TABLE
IDStatus
1A
2B
3C

With 1M Records. I have filtered to filter all with status C. So it just speed up my refresh process because it only process 500 rows.

 

I have also the Task table:

TASK TABLE
IDTicket IDStatus
11A
21B
33C

 

This one haves let's say 5M rows, and I need to filter it and only  keep the data of the Tickets which are relevant (The ones that where not filtered before) In this case, Task with ID 3 should be removed. so instad of processing 5M entries, I will just process 5K.

 

 

I have tried to create a new column, but each formula I set to try to get the data it does not work, the IDE does not let me set a VLOOKUP or Related formula even an IF.

Simpliet way is pseudo code will be creating a new column:
If (Exists in Ticket Table (ID=Task.TicketID), 1,0)
DELETE ROW IF Exists=0

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Chavas - If you are having issues with RELATEd, try RELATEDTABLE instead. 

  • Chavas's avatar
    Chavas
    Helper II
    5 years ago

    Greg_Deckler  IDE does not allow me to use that functions

     

     

     

    I might be really lost at some point

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      Chavas - Oh, you are in Power Query, sorry, those commands I mentioned are DAX. Well, in Power Query, did you try the Left-Anti or Right-Anti joins? Let me look at this with a fresh set of eyes.

      • Chavas's avatar
        Chavas
        Helper II
        5 years ago

        Thank you both ( Greg_Deckler  and amitchandak ) for your assistance, what I am trying, it is something like this

        SELECT TaskID, DATABASE.dbo.Ticket.TicketID, DATABASE.dbo.Ticket.Status FROM DATABASE.dbo.Task
        inner join Database.dbo.Ticket on DATABASE.dbo.Task.ParentID=Ticket.ID
        WHERE Ticket.Status != 'Closed'

        So I have already filtered the Ticket table but I wont to prevent loading tons of rows which I do not need and I am not going to use it. So I do not need to have on a new table all the info of the Tickets, you know? I would like to keep it separated and then I will manage it through the relations.

         

        I might posted it in the wrong forum :S And sorry for the confusion