Remove rows from table if Number is not present in column of another table

[CraigDawson]

I have a power BI with 2 tables, one that stores data about "Evaluations" and another that stores the answers to questions in the evaluation. The answers are stored one per row so it has 16 rows per evaluation and they are linked by a column in the answers table called "Evaluation ID" which relates directly to the "ID" column in the Evaluations table.

I have 2 types of evaluations, Normal and Calibration evaluations. This is noted in the Evaluations table and in the report I am creating at the moment, I have filtered for just the Normal evaluations in the evaluations table. The problem is, I need to remove all of the related answers from the Answers table so I need to find a way to remove all rows from the answers table where "Evaluation ID" is not present in the "ID" column of the Evaluations table. Is this possible?

[ToddChitt]

Oooh, SharePoint list. I feel your pain. No, I don't think you can make that kind of join in a webcall. But then again, I don't use SharePoint for this type of stuff so don't take my word on it. 

[ToddChitt]

What is your data source type? If you are lucky to have SQL Server or another RDBMS, you would re-write the logic in a view that would get evaluated by the source (database server), making it faster than evaluating at the client (Power BI). 

Best of luck.

[CraigDawson]

No such luck, working off a sharepoint list because it works free with powerapps. Can't even get the budget for powerapps premium so we can use dataverse. Would a comparative filter like that be possible with a sharepoint webcall?

[ToddChitt · Accepted solution]

Since this is the Power Query forum, I will give you a Power Query answer: Do a MERGE on the Answers table and MERGE JOIN it to the Evaluations table with an INNER JOIN. 

That sounds pretty simple, is that all you need? Hope that helps.

[CraigDawson]

Yup it was that simple, hoping that won't be too resource intensive one there's thousands of answers stored but it works for now certainly. I can always modify the dataset to include the evaluation type on creation in PowerApps if it does end up taking too long the refresh.

[Chakravarthy]

Hi @CraigDawson - Could you please share the pbix file?