Forum Discussion
Replace multiple values in multiple columns in one step
- Anonymous6 years ago
if we had a replacerAny.ReplaceValue function ..., we could do it all in one step with the table.replace function.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcYxDQAACAMwL7t52EANwb8NFnp1BolAJjYGdMmvXOlbbtW33W7sHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [prima = _t, seconda = _t]), cols=Table.ColumnNames(Source), Dict=#table({"old","new"},{{1,"uno"},{3,"tre"},{11,"undici"}}), replaced=Table.ReplaceValue( Source, Dict[old],Dict[new], replacerAnyReplaceValue, cols ) in replacedin the meantime we can use this:
let rep=(value, old as list , new as list )=> let Dict=Record.FromList(List.Transform(new, Text.From), List.Transform(old, Text.From)), Replacements= Record.FieldOrDefault(Dict,value,value) in Replacements in rep
Hi there,
Replacing values in multiple columns is not the easiest task. One way is to use unpivot and to replace items using List.ReplaceMatchingItems. You can then reference the TranslationTable by combining List.ReplaceMatchingItems with List.Zip. I write about all this right here:
https://gorilla.bi/power-query/replace-values/
Hope that helps!
Rick
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.