Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chavas
Helper II
Helper II

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

1 ACCEPTED SOLUTION

@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

AdvanceProperty.png

View solution in original post

15 REPLIES 15
amitchandak
Super User
Super User

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  IDE does not allow me to use that functions

Chavas_0-1599488892040.png

 

 

 

I might be really lost at some point

@ImkeF , @edhans , can you help on this 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@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

AdvanceProperty.png

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

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

edhans_0-1599496117031.png

 

Any questions ping back.

 

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Yay!
That is a solution!
But the problem is that I have to hardcode the status in the query. In a future, If I want to change it, I will need to redo the query or minding that I have hardcoded somewhere.

 

Is there any other fancy/more elegant option?

(I am quite new to PowerBI, so your solution might be the way)

Copied. From now I will perform the short path as far as the clock is bitting me.

 

Thanks for the assistance!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.