Transform Values taken from a Lookup Table

[WALEED]

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.

[Zubair_Muhammad]

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

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

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 · Accepted solution]

HI @WALEED

Yes in that case Power Query is the choice

See the attached file here fro Power Query solution

[WALEED]

You, sir, are a LIFESAVER!!

Many thanks for the effort you've put into creating that example pbix.

I'll share the formulae below: (I hope you don't mind) for the benefit of others.

let
    Source = Excel.Workbook(File.Contents("C:\Users\hp\Desktop\Forum Posts\PowerBI\Transform Values taken from a Lookup Table.xlsx"), null, true),
    SourceTable_Table = Source{[Item="SourceTable",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(SourceTable_Table,{{"INDEX", type text}, {"FIELD 1", type text}, {"FIELD 2", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"INDEX"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns",{"Attribute", "Value"},LookUpTable,{"FIELD NAME", "OLD VALUE"},"LookUpTable",JoinKind.LeftOuter),
    #"Expanded LookUpTable" = Table.ExpandTableColumn(#"Merged Queries", "LookUpTable", {"NEW VALUE"}, {"LookUpTable.NEW VALUE"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded LookUpTable", each true),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Revised Values", each if [LookUpTable.NEW VALUE]=null then [Value] else [LookUpTable.NEW VALUE]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"INDEX", "Attribute", "Revised Values", "Value", "LookUpTable.NEW VALUE"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Value", "LookUpTable.NEW VALUE"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Revised Values")
in
    #"Pivoted Column"

[Zubair_Muhammad]

@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

[Zubair_Muhammad]

@WALEED

You can follow the remaining steps from the Query Editor.

This is the end result.