Forum Discussion

KNP's avatar
KNP
Super User
6 years ago
Solved

Replace Values in Column Conditionally using Rules Table

Hi,   There are a few similar posts like this but I haven't been able to adapt them to solve my problem. I'm looking to create a function that will do this replacement so that it can be dynamic.  ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    test if this is right for you:

     

     

     Table.FromRecords(Table.TransformRows(dataTab, (x)=> Record.Combine({x,[City=
(try Table.SelectRows(ruleTab, each ([Customer]=x[Customer] or x[Customer]="") and ([location]=x[location] or x[location]="" ))[ActualCity]{0} otherwise x[City])]})))

     

KNP's avatar
KNP
Super User
6 years ago

Thanks Anonymous , your first solution works so thank you for that. I would like to dive a little deeper into your second solution as I think it will be more future proof. I'm getting some results that don't match what I'm expecting and I think it is because I don't fully understand how it is working.

See screenshots below. In this example I would have wanted the City to stay as is (row 7 of the results) i.e. Wellington, like it has for row 4 in the results below (Auckland). Are you able to help my understand your code a little more?

 

Source (highlight = the row that shouldn't change):

Rules (highlight = the rule being applied to the result):

 Results:

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

The problem could be the interpretation (mine or yours) of the "relevant columns" 😀.
Which columns do you intend to use as a key to select the rows on which to make the changes?

 

Another important aspect to be clarified is related to the logical consistency of the rules.
Take, for example, rules 4 and 5. If there is a record in the data table that has city = Wellington, customer = ZZZ and location = North, what do you do?

 

  • KNP's avatar
    KNP
    Super User
    6 years ago

    The relevant columns would be any in the rules table except Id and ActualCity if not blank.

    City will always be populated. The other columns would be optional. 

    Blank should be ignored so only columns that have values should be used to evaluate the data table. 

    I'm happy to unpivot the rules if it makes it easier. 

     

    I think the short answer to your second question is that the rules won't exist like that (or data won't).

    What would happen in your code if they did? Process twice (in order) therefore getting the second result after processing is complete or only process once and then not match again? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      It seems to me that works,although in my test I used tables with fewer rows and columns than those shown by you.

      Perhaps you should make typeof your column text type and modify, if any, "null" cell to empty cell, i.e. "".

      Here how (little change to the previuos version):

       

       

      let
    relCols={"Customer","Location","Supplier"},
    matchCity=(rTab,dTabRow,rCols) => [ dTabRowValues= Record.FieldValues(Record.SelectFields(dTabRow,rCols)), 
                                        newCity= try Table.SelectRows(rTab, each List.ContainsAll( Record.FieldValues(_),dTabRowValues))[ActualCity]{0} otherwise dTabRow[City]] 
                                        [newCity]
in Table.FromRecords(Table.TransformRows(dataTab, each _&[City=matchCity(ruleTab,_,relCols)]))

       

       

       

       

      About your question:

      What would happen in your code if they did? Process twice (in order) therefore getting the second result after processing is complete or only process once and then not match again?

       

      this piece of code:

       

       

      Table.SelectRows(rTab, each List.ContainsAll( Record.FieldValues(_),dTabRowValues))[ActualCity]{0}

       

       

      takes the first matched rule. but you can eventually change the behavior

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      It seems to me that works. perhaps you should change column type to text and cell value "null" to empty, i.e. ""

       

      here how (with little change), although I used fewer rows and fewer columns than you used:

       

       

       

       

      let
    relCols={"Customer","Location","Supplier"},
    matchCity=(rTab,dTabRow,rCols) => [ dTabRowValues= Record.FieldValues(Record.SelectFields(dTabRow,rCols)), 
                                        newCity= try Table.SelectRows(rTab, each List.ContainsAll( Record.FieldValues(_),dTabRowValues))[ActualCity]{0} otherwise dTabRow[City]] 
                                        [newCity]
in Table.FromRecords(Table.TransformRows(dataTab, each _&[City=matchCity(ruleTab,_,relCols)]))

       

       

       

       

      about your question

      What would happen in your code if they did? Process twice (in order) therefore getting the second result after processing is complete or only process once and then not match again?
      the following piece of code

       

       

      Table.SelectRows(rTab, each List.ContainsAll( Record.FieldValues(_),dTabRowValues))[ActualCity]{0}

       


      select the first of the rules that the current row of the data table meets.

      But this behavior can eventually be changed

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        this version should take into account the type of columns and the list of irrelevant chars / text (like: null, empy, blank, and so on).
        So maybe 😁you shouldn't worry about it 😀.

         

         

        let
    relCols={"Customer","Location","Supplier"},
    irrFieldValues={""," ",null, "QuelloCheVogliO"},

    MyList.ContainsAll=[MLCA=(listRules,listData,nullChars)=> List.ContainsAll(listRules,listData, 
                        (x,y)=>List.Contains({Text.From(x)}&irrFieldValues,Text.From(y)))][MLCA],

    matchCity=(rTab,dTabRow,rCols) => [ dTabRowValues= Record.FieldValues(Record.SelectFields(dTabRow,rCols)), 
                                        newCity= try Table.SelectRows(rTab, each MyList.ContainsAll( Record.FieldValues(_),dTabRowValues,irrFieldValues))[ActualCity]{0} otherwise dTabRow[City]] 
                                        [newCity]
    
in Table.FromRecords(Table.TransformRows(dataTab, each _&[City=matchCity(ruleTab,_,relCols)]))

         

        T