Apply rules on a table in Power Query

[vassdavid]

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

[parry2k]

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

[vassdavid]

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