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

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

     

  • 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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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