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