Forum Discussion
Solved
Replace Values in Column Conditionally using Rules Table
Hi, There are a few similar posts like this but I haven't been able to adapt them to solve my problem. I'm looking to create a function that will do this replacement so that it can be dynamic. ...
test if this is right for you:
Table.FromRecords(Table.TransformRows(dataTab, (x)=> Record.Combine({x,[City= (try Table.SelectRows(ruleTab, each ([Customer]=x[Customer] or x[Customer]="") and ([location]=x[location] or x[location]="" ))[ActualCity]{0} otherwise x[City])]})))
test if this is right for you:
Table.FromRecords(Table.TransformRows(dataTab, (x)=> Record.Combine({x,[City=
(try Table.SelectRows(ruleTab, each ([Customer]=x[Customer] or x[Customer]="") and ([location]=x[location] or x[location]="" ))[ActualCity]{0} otherwise x[City])]})))
Anonymous - Thanks for all your help on this. After much testing I've decided to go with a variation of your first answer. The others were very promising but I was getting mixed results. Performance was certainly a consideration on a relatively large dataset.