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

     

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