Forum Discussion
Anonymous
6 years agoNot applicable
Replace multiple columns value base on a configuration table
My situation as below: I have a Job table that contains Region and Area data of jobs: and a configuration table I want to transform data of the Job table base on ste...
Anonymous
6 years agoNot applicable
I'm not sure I understand what the need is. But perhaps the following function, originally designed for a similar problem, could be a good starting point, appropriately adapting the names of the columns
let
match=(rTab,dTabRow,rCols) =>
let
dTabRowValues= Record.FieldValues(Record.SelectFields(dTabRow,rCols)),
newCity= try Table.SelectRows(rTab, each List.ContainsAll( Record.FieldValues(_),dTabRowValues))[ActualCity]{0} otherwise dTabRow[City]
in
newCity
in
match
Here how to call the function:
for each row of data table search a matching roe in the configuration table (comparing only relevant column value) and replacing old value with new value
Table.FromRecords(Table.TransformRows(dataTab, each _&[City=matchCity(ruleTab,_,relCols)]))