Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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...
Need Help
mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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