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.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        WALEED

         

        Here are the steps for Power Query Solution

         

        Step #1: select Field Columns in Source Table and unpivot them

         

         

        Step 2: Perform a Merge Query using 2 fields each from Source Table and LookUp Table