Forum Discussion
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...
Thanks for your suggestion. But, I got below error with the first query:
Besides, your solution seems to replace Region first, then replace Area. I would need to replace them in the order of the configuration file.
try and test (I'm not sure I understand your needs and filtering rules) these:
let
nsteps=Table.RowCount(ruleTab),
steps=List.Accumulate({1..5},dataTab,
(s,c)=>Table.FromRecords(Table.TransformRows(s, each _&[region=toRegion(ruleTab{c-1},_),area=toArea(ruleTab{c-1},_)])))
in
steps
toRegion function
let
match=(stepN,dTabRow) =>
let
dTabRowValues= Record.FieldValues(Record.SelectFields(dTabRow,{"region"})){0},
newValue= if stepN[from region]=dTabRowValues then stepN[to region] else dTabRow[region]
in
newValue
in
match
and similar toArea function:
let
match=(stepN,dTabRow) =>
let
dTabRowValues= Record.FieldValues(Record.SelectFields(dTabRow,{"area"})){0},
newValue= if stepN[from area]=dTabRowValues then stepN[to area] else dTabRow[area]
in
newValue
in
match
this if you intend to have all the intermediate steps and not just the final result:
let
nsteps=Table.RowCount(ruleTab),
steps=List.Accumulate({1..5},{dataTab},
(s,c)=>s&{Table.FromRecords(Table.TransformRows(List.Last(s), each _&[region=toRegion(ruleTab{c-1},_),area=toArea(ruleTab{c-1},_)]))})
in
steps
let me know if this is what you were looking for