Forum Discussion
Filter Rows which don't start with letter defined in another table
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
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:
Hope this helps!
Theo
5 Replies
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
- TheoC
Community Champion
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:
Hope this helps!
Theo
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.
Works like a charm. Thank you!!
