Forum Discussion
Replace Values in Column Conditionally using Rules Table
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])]})))
this solution tries to allow control over the list of relevant columns:
let
relCols={"Customer","location"},
matchCity=(rTab,dTabRow,rCols) => [ dTabRowValues= Record.FieldValues(Record.SelectFields(dTabRow,rCols)),
newCity= try Table.SelectRows(rTab, each List.ContainsAll( Record.FieldValues(_),dTabRowValues))[ActualCity]{0}
otherwise dTabRow[City]] [newCity],
tab=Table.FromRecords(Table.TransformRows(dataTab, each _&[City=matchCity(ruleTab,_,relCols)]))
in
tab
Thanks Anonymous , your first solution works so thank you for that. I would like to dive a little deeper into your second solution as I think it will be more future proof. I'm getting some results that don't match what I'm expecting and I think it is because I don't fully understand how it is working.
See screenshots below. In this example I would have wanted the City to stay as is (row 7 of the results) i.e. Wellington, like it has for row 4 in the results below (Auckland). Are you able to help my understand your code a little more?
Source (highlight = the row that shouldn't change):
Rules (highlight = the rule being applied to the result):
Results:
The problem could be the interpretation (mine or yours) of the "relevant columns" 😀.
Which columns do you intend to use as a key to select the rows on which to make the changes?Another important aspect to be clarified is related to the logical consistency of the rules.
Take, for example, rules 4 and 5. If there is a record in the data table that has city = Wellington, customer = ZZZ and location = North, what do you do?The relevant columns would be any in the rules table except Id and ActualCity if not blank.
City will always be populated. The other columns would be optional.
Blank should be ignored so only columns that have values should be used to evaluate the data table.
I'm happy to unpivot the rules if it makes it easier.
I think the short answer to your second question is that the rules won't exist like that (or data won't).
What would happen in your code if they did? Process twice (in order) therefore getting the second result after processing is complete or only process once and then not match again?
It seems to me that works,although in my test I used tables with fewer rows and columns than those shown by you.
Perhaps you should make typeof your column text type and modify, if any, "null" cell to empty cell, i.e. "".
Here how (little change to the previuos version):
let relCols={"Customer","Location","Supplier"}, matchCity=(rTab,dTabRow,rCols) => [ dTabRowValues= Record.FieldValues(Record.SelectFields(dTabRow,rCols)), newCity= try Table.SelectRows(rTab, each List.ContainsAll( Record.FieldValues(_),dTabRowValues))[ActualCity]{0} otherwise dTabRow[City]] [newCity] in Table.FromRecords(Table.TransformRows(dataTab, each _&[City=matchCity(ruleTab,_,relCols)]))About your question:
What would happen in your code if they did? Process twice (in order) therefore getting the second result after processing is complete or only process once and then not match again?
this piece of code:
Table.SelectRows(rTab, each List.ContainsAll( Record.FieldValues(_),dTabRowValues))[ActualCity]{0}takes the first matched rule. but you can eventually change the behavior