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

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