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

 

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

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        WALEED

         

        You can follow the remaining steps from the Query Editor.

         

        This is the end result.

         

    • WALEED's avatar
      WALEED
      Advocate II
      8 years ago

      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"