Forum Discussion
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.
Sample Data:
= 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])
I want to conditionally replace values in the City column using the Rules table.
Rules:
= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsTc7OScxLATGdnIEkEBkaGYO42Tl++UUlGUqxOtFKRjiUmpiaQZQG55dClRrjUGpuYQlRGp5aXAJWaQLkh6fm5GTmpZfk50FUQawEM4FSCAeYoquNioqCKINIQO2PBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, City = _t, Customer = _t, Location = _t, Supplier = _t, ActualCity = _t])
I would like to use all the columns in the Rules table (except the Id and ActualCity) to compare with the Data table. The Rules table will grow including new columns. Blanks can be ignored, e.g. Rule 4 only needs to use 'City' and 'Location' to check the Data table. For all matching rows, replace the values in the City column with the 'ActualCity' value.
Desired Result:
= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc+9CsMgEADgd3HOov0fNWnHLh0CBodipErEQKLvXzU1vXQoHMfhfdydXYfoYO/j5DWqEGV1zDEw2cVMCEk1xkhU2T3GsHX7wxG6FbZq9tCdzpfFbScGOdin6+NTc719NsdeknBgq9YLY4Ayq0LKcZzzpVs2bpQ17uVHt4rv+VnUejKzlzpMMn+UUsDgTf8gdE1wqjdpH2PslyAh3g==", 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])
I'm happy to unpivot the rules if it makes it easier to solve the problem:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsTc7OScxLATGdnIEkEBkaGYO42Tl++UUlGUqxOtFKRjiUmpiaQZQG55dClRrjUGpuYQlRGp5aXAJWaQLkh6fm5GTmpZfk50FUQawEM4FSCAeYoquNioqCKINIQO2PBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, City = _t, Customer = _t, Location = _t, Supplier = _t, ActualCity = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Customer", "Location", "Supplier"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Id", "ActualCity"}, "Attribute", "Value")
in
#"Unpivoted Columns"
I'd really appreciate any help from the M code gurus out there.
Regards,
Kim
10 Replies
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 resultthis solution tries to allow control over the list of relevant columns:
let relCols={"Customer","location"}, 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], tab=Table.FromRecords(Table.TransformRows(dataTab, each _&[City=matchCity(ruleTab,_,relCols)])) in tabThanks 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:
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?
Anonymous - Thanks for all your help on this. After much testing I've decided to go with a variation of your first answer. The others were very promising but I was getting mixed results. Performance was certainly a consideration on a relatively large dataset.
