cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
WALEED Regular Visitor
Regular Visitor

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 1FIELD 2
BOB001NOT AVAILABLE.3 mm
BOB002ABCN/A
BOB003DEGBLA BLA BLA

 

Lookup table:

FIELD NAMEOLD VALUENEW VALUE
FIELD 1NOT AVAILABLENA
FIELD 1DEGDEF
FIELD 2.3 mm0.3 mm
FIELD 2N/ANA

 

Result:

TAGFIELD 1FIELD 2
BOB001NA0.3 mm
BOB002ABCNA
BOB003DEFBLA 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Transform Values taken from a Lookup Table

HI @WALEED

 

Yes in that case Power Query is the choice

 

See the attached file here fro Power Query solution

7 REPLIES 7
Super User
Super User

Re: Transform Values taken from a Lookup Table

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

1021.png

Super User
Super User

Re: Transform Values taken from a Lookup Table

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

1022.png

WALEED Regular Visitor
Regular Visitor

Re: Transform Values taken from a Lookup Table

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.

Super User
Super User

Re: Transform Values taken from a Lookup Table

HI @WALEED

 

Yes in that case Power Query is the choice

 

See the attached file here fro Power Query solution

Super User
Super User

Re: Transform Values taken from a Lookup Table

@WALEED

 

Here are the steps for Power Query Solution

 

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

 

1023.png

 

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

1024.png

Super User
Super User

Re: Transform Values taken from a Lookup Table

@WALEED

 

You can follow the remaining steps from the Query Editor.

 

This is the end result.

 

1025.png

WALEED Regular Visitor
Regular Visitor

Re: 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"