Forum Discussion
Transform Values taken from a Lookup Table
This is a tough. Please help!
I'm trying to change multiple values in multiple fields on a table. I have another table which has the name of fields, old values, and new values. e.g.
Source Table:
INDEX | FIELD 1 | FIELD 2 |
| BOB001 | NOT AVAILABLE | .3 mm |
| BOB002 | ABC | N/A |
| BOB003 | DEG | BLA BLA BLA |
Lookup table:
| FIELD NAME | OLD VALUE | NEW VALUE |
| FIELD 1 | NOT AVAILABLE | NA |
| FIELD 1 | DEG | DEF |
| FIELD 2 | .3 mm | 0.3 mm |
| FIELD 2 | N/A | NA |
Result:
| TAG | FIELD 1 | FIELD 2 |
| BOB001 | NA | 0.3 mm |
| BOB002 | ABC | NA |
| BOB003 | DEF | BLA BLA BLA |
The lookup table is BIG and also gets changed and appended often. That's why I don't want to do the changes using an old school formula.
HI WALEED
Yes in that case Power Query is the choice
See the attached file here fro Power Query solution
7 Replies
Do you want a calculated column, NEW TABLE, MEASURE?
For example you can add following Calculated Column in Source Table
Revised Field 1 = LOOKUPVALUE ( LookUpTable[NEW VALUE], LookUpTable[OLD VALUE], SourceTable[FIELD 1], LookUpTable[FIELD NAME], "FIELD 1" )In case you want the Old Value when there is no corresponding NEW value, you can use this
Revised Field 1 = VAR myvalue = LOOKUPVALUE ( LookUpTable[NEW VALUE], LookUpTable[OLD VALUE], SourceTable[FIELD 1], LookUpTable[FIELD NAME], "FIELD 1" ) RETURN IF ( ISBLANK ( myvalue ), SourceTable[FIELD 1], myvalue )Many thanks; please allow me to pick your brain a little more:
I'm using Power BI. will this work in Power Query?
Is there a way to identify the field name using the first column of the lookup table?
And finally, replace the original column with the transformed one.
The reason I want it to be dynamic is there are 243 ever-changing fields.
