Forum Discussion
elaj
Helper IV
Helper IVReplace multiple values in multiple columns in one step
Hi, i have a table like that: and i want to replace the string values to numbers like that: "Last 4 weeks" = 1 "2 months ago" = 2 "3 months ago" = 3 "4 months ago" = 4 ... etc is...
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
Another way could be this wich use table.replacevalue 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]),
S=Table.ToRows(Source),
cols=Table.ColumnNames(Source),
Dict=[1="uno",3="tre",11="undici",33="tre_e_tre"],
replaced=List.Accumulate(Record.FieldNames(Dict), Source, (s,c)=> Table.ReplaceValue(
s,
c,
Record.Field(Dict,c),
Replacer.ReplaceValue,
cols
))
in
replaced
I take this opportunity to point out the different result obtained with Replace.ReplacerText instead of Replace.Replacervalue.