Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All!
I'd like to apply rules on a Power BI dataset. Is this possible?
Example:
1. table is the 'Logic file' (Excel) where the rules are stored:
Company code | Report | Name of the column serving the basis of data change that need to be done #1 | Data that should be changed #1 | Name of the column serving the basis of data change that need to be done #2 | Data that should be changed #2 | Name of the column serving the basis of data change that need to be done #3 | Data that should be changed #3 | Name of the column(s) in which the data need to be changed | Change to |
XX01 | Arrivals | Description | Naphta | Commodity code | 11 | ||||
ZZ02 | Arrivals | Commodity Code | (blank) | IR Amount(LC) | >0 | IR Amount(LC) | ≤500 | Commodity Code | 104 |
XX01 | Dispatch | Mode of Trans. | <1 | Mode of Trans. | 3 | ||||
XX01 | Dispatch | Mode of Trans. | >8 | Mode of Trans. | 3 | ||||
YY01 | Arrivals | Material Ctry Orig | (blank) | Material Ctry Orig | =Orig.country | ||||
YY01 | Arrivals | Material Ctry Orig | YY | Material Ctry Orig | =Orig.country |
2. table is the report (multiple Excel files appended in Power Query) where the rules need to be applied:
Report | Commodity Code | Company Code | Description | Mode of Trans. | IR Amount(LC) | Material Ctry Orig | Orig.country |
Arrivals | 1234567 | XX01 | dummy | 7 | 1,000,000.00 | ||
Arrivals | 1234567 | XX01 | dummy | 7 | 2,000.00 | ||
Dispatch | 1234567 | ZZ02 | dummy | 7 | 1,000,000.00 | ||
Arrivals | 1234567 | XX01 | Naphta | 7 | 1,000,000.00 | ||
Arrivals | ZZ02 | dummy | 7 | 100.00 | |||
Arrivals | 1234567 | YY01 | dummy | 7 | 600.00 | Belgium | |
Arrivals | 1234567 | YY01 | dummy | 7 | 500.00 | YY | Belgium |
Expected result:
Report | Commodity Code | Company Code | Description | Mode of Trans. | IR Amount(LC) | Material Ctry Orig | Orig.country |
Arrivals | 1234567 | XX01 | dummy | 7 | 1,000,000.00 | ||
Arrivals | 1234567 | XX01 | dummy | 7 | 2,000.00 | ||
Dispatch | 1234567 | ZZ02 | dummy | 7 | 1,000,000.00 | ||
Arrivals | 11 | XX01 | Naphta | 7 | 1,000,000.00 | ||
Arrivals | 104 | ZZ02 | dummy | 7 | 100.00 | ||
Arrivals | 1234567 | YY01 | dummy | 7 | 600.00 | Belgium | Belgium |
Arrivals | 1234567 | YY01 | dummy | 7 | 500.00 | Belgium | Belgium |
Maybe it'd be easier to achieve this by writing a macro in Excel, but I only would like to do that if there's no other way.
Any suggestions would be greatly appreciated.
Thank you in advance!
David
@vassdavid I don;t think you can read rules from a file and apply it, you can surely manually add the steps in power query for each rule.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2kThanks for the quick reply. I will consider the manual solution, but I'm afraid that won't be the best in this case. We're still collecting, but we're going to have around 500 rules I'd say.