Forum Discussion
Remove rows based on another table
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
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
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.- Create a duplicate of your Ticket table and call it TicketIDs
- 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]).
- Go back to the Task table.
- 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
- Table.SelectRows(Source, each ([Ticket ID] = "1"))
- You need to replace that with the following code:
- 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.
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!
Sorry for repositng, but it is possible that if you do it though the SQL statemet it takes ages to pull the data?
