Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
HI @WALEED
Yes in that case Power Query is the choice
See the attached file here fro Power Query solution
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" )
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 )
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.
HI @WALEED
Yes in that case Power Query is the choice
See the attached file here fro Power Query solution
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"
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
You can follow the remaining steps from the Query Editor.
This is the end result.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |