Replace multiple columns value base on a configuration table

[Anonymous]

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 steps from the configuration table. Like below:

After step 1: 

After step 2:

After step 3:

After step 4:

After step 5:

I would like to dynamically load the configuration file from a CSV file so that I can add step 6,7,8, etc. at runtime.

I wonder if we can do that with Power Query.

Thanks in advanced

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[mahoneypat]

This looks like it can be solved with two bulk replaces.  Please paste the 3 M queries into 3 blank queries to see how to do that with your example data.  Two of the queries are the replace lists (of lists) from your Configuration table and the third is your main table that gets the values replaced.

Query 1

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxNLcpMTlTSUQpPLS5RcM5PLC5RitVBlnBNRJFwTsxLTAGJB+eXlmSkFuUpAEWAAqiaHHMSi7PRBZ1T80qKEnMUHItSgVKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Area = _t]),
ReplaceWithList1 = Table.TransformColumns(Source, {{"Region", each Text.Combine(List.ReplaceMatchingItems({_}, ReplaceList1)), type text}}),
ReplaceWithList2 = Table.TransformColumns(Source, {{"Area", each Text.Combine(List.ReplaceMatchingItems({_}, ReplaceList2)), type text}})
in
ReplaceWithList2

Query 2

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMTS3KTE4EssJTi0sUnPMTi0swhR2LUhOVYnWilYxQ5FwTsWoBC8O1GKPIAZFfflFJhgKSEEiVCZDhnJiXmIJPkSkWCcecxOLsRCwSUD7YGbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Step No" = _t, #"From Region" = _t, #"From Area" = _t, #"To Region" = _t, #"To Area" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Step No", Int64.Type}, {"From Region", type text}, {"From Area", type text}, {"To Region", type text}, {"To Area", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"From Region", "To Region"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each {[From Region],[To Region]}),
#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([To Region] = "North America") and ([From Region] <> "North America")),
Custom = #"Filtered Rows"[Custom]
in
Custom

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMTS3KTE4EssJTi0sUnPMTi0swhR2LUhOVYnWilYxQ5FwTsWoBC8O1GKPIAZFfflFJhgKSEEiVCZDhnJiXmIJPkSkWCcecxOLsRCwSUD7YGbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Step No" = _t, #"From Region" = _t, #"From Area" = _t, #"To Region" = _t, #"To Area" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Step No", Int64.Type}, {"From Region", type text}, {"From Area", type text}, {"To Region", type text}, {"To Area", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"From Area", "To Area"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each {[From Area],[To Area]}),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([To Area] <> "")),
Custom = #"Filtered Rows1"[Custom]
in
Custom

Query 3

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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)]))