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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
nganguyensyntax
Frequent Visitor

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

nganguyensyntax_0-1705425886147.png

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.

nganguyensyntax_1-1705425935104.png

This is the result I'm looking for:

nganguyensyntax_2-1705426218052.png

Is there any way to exclude transactions like this?

 

Thanks

 

 

1 ACCEPTED SOLUTION
TheoC
Super User
Super User

Hi @nganguyensyntax 

 

If @bchager6'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.

 

TheoC_0-1705434079751.png

 

The output will look like this:

 

TheoC_1-1705434918990.png

 

Example File.pbix

 

Hope this helps!

 

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

5 REPLIES 5
TheoC
Super User
Super User

Hi @nganguyensyntax 

 

If @bchager6'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.

 

TheoC_0-1705434079751.png

 

The output will look like this:

 

TheoC_1-1705434918990.png

 

Example File.pbix

 

Hope this helps!

 

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Works like a charm. Thank you!!

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

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

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

bchager6
Super User
Super User

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

bchager6_1-1705433008200.png

 

Then filter out the "No" columns

bchager6_2-1705433054951.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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