Forum Discussion

WALEED's avatar
WALEED
Advocate II
8 years ago
Solved

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 T...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

WALEED

 

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"
)

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    WALEED

     

    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 )

    • WALEED's avatar
      WALEED
      Advocate II
      8 years ago

      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.