Forum Discussion
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, with contains its own ID and the ParentID which is the ticket ID where the task is linked to
I have filtered the Tickets table to keep only the status not closed.
Nevertheless regarding the tasks I need to remove the rows based on the tickets remaining on the Tickets table so I can filter it properly.
I have thought about some ways of doing it, but it looks like the syntax is not correct. It looks like the columns created on the Query Editor are quite limited.
Any suggestion on how to do it so I can prevent to get 500.000 rows each refresh where 95% are not relevant for the dashboard I am working on?
Thanks in advance
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
15 Replies
Chavas - Not entirely clear on this, sample data and expected output would help. Maybe something like EXCEPT?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.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 ID Status 1 A 2 B 3 C 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 ID Ticket ID Status 1 1 A 2 1 B 3 3 C 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=0Chavas - If you are having issues with RELATEd, try RELATEDTABLE instead.
Chavas , as far as I know, the Power Query way to merge the table using an inner join, if need create a column using header and filter blank
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
So merge the header(after rows from header deleted) with details using inner join and expand and take status from the header. Ideally, it should reduce the rows. If not filter the rows here too based on the status.
Thansk amitchandak I have tried with the Merge option, but it does not work because I want to prevent getting ALL the data each refresh. So this needs to process everything but I want to remove the rows based on a condition of: If Task.Value=Incident.Value, keep row, remove row.
