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

     

ziying35's avatar
ziying35
Impactful Individual
6 years ago

Hi, KNP 

my code as below:

 

// Output
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc5NC8MgDAbg/+K5F933MbbbsdeBxcOwZcrEgtP/X53bCNschBDIQ94MA4GobvbiRtIQ4G3qqShbpc4YyzOlRDa/3Xqzxa4Od/tDgbWT3fH0jE67LPHF82Stcdcwu2L62Qddxof7RkKIsn+lVhxGb9Fqb+5B6ehVTgEAxPBf/yB2XXTTaHIe5/yTECkX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Customer = _t, Location = _t, Supplier = _t, Data1 = _t, Data2 = _t]),
    chType = Table.TransformColumnTypes(Source,{{"City", type text}, {"Customer", type text}, {"Location", type text}, {"Supplier", Int64.Type}, {"Data1", Int64.Type}, {"Data2", Int64.Type}}),
    rules = Table.Buffer(Rules),
    fx = (rec, matchlist)=>try rules[ActualCity]{Table.PositionOf(rules, rec, 0, matchlist )} otherwise rec[City],
    trans = Table.TransformRows(chType,each let m =List.FirstN(Record.FieldNames(_),4), r=Record.SelectFields(_, m) in _&[City= fx(r, m)]),
    result = Table.FromRecords(trans)
in
    result