Filter Rows which don't start with letter defined in another table

[nganguyensyntax]

Hello,

I have a table, let's call it Transaction. This has 2 columns Customer ID and Transaction

I have another table called Rule, whose ID is connected to Customer column in the other table. The rule here is excluding transactions from Transaction table if the Transaction starts with a letter defined in Rule. For example, if a transaction of customer 1 starts with "E", it should be removed from the dataset.

This is the result I'm looking for:

Is there any way to exclude transactions like this?

Thanks

[TheoC · Accepted solution]

Hi @nganguyensyntax 

If @Anonymous's solution doesn't work, I recommend using Power Query to achieve this.  You can do this with a couple of transformations and then using Merge to create a new table with the desired output.

You can refer to the Applied Steps in each of the respective tables to see what I did to get the output. However, the most important thing is that prior to Merging the tables, you create a new column in each table that will be used as the relationship that ensures the merge occurs correctly.  I have created the 'validityCheck' column in each table and that is the basis of the Merge.

The output will look like this:

Example File.pbix

Hope this helps!

Theo

[nganguyensyntax]

Works like a charm. Thank you!!

[Anonymous]

@TheoC  I recommended using the Query Editor (Power Query) and it will work. 😊

[TheoC]

@Anonymous  I agree with you that Query Editor / Power Query can achieve it with ease. It's just making sure that there is clarity in establishing a relationship between the Rule and Transaction table to allow the filerting process to occur effectively.

[Anonymous]

@nganguyensyntax  You could merge the tables on ID and Customer and create an "Include Y/N" custom column in the query editor to filter out rows. Using sample data:

Then filter out the "No" columns