Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to Solution.
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
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
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
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!!
@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.
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
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
116 | |
82 | |
47 | |
42 | |
34 |
User | Count |
---|---|
186 | |
80 | |
72 | |
48 | |
45 |