Transform Values taken from a Lookup Table

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"