Forum Discussion

elaj's avatar
elaj
Icon for Helper IV rankHelper IV
6 years ago
Solved

Replace 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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 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
    replaced

     

     

     

    in 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

     

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

try to adapt this

 

 

let

Dict = [a="apple",b="banana",l="lemon"],
TurnTextToList = Table.AddColumn(Text, "Custom", each Text.Split([Text], " ")),
Replacements = Table.AddColumn(TurnTextToList, "Changed Text Expected", each Text.Combine(List.Transform([Custom],each Record.FieldOrDefault(Dict,_,_))," "))


in
Replacements

 

 

using this function:

Table.ReplaceValue(
    Table.FromRecords({
        [a = 1, b = "hello"],
        [a = 3, b = "goodbye"]
    }),
    "goodbye",
    "world",
    Replacer.ReplaceText,
    {"b"}
)

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

a draft from which to start:

 

 

let
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"],

    Replacements = List.Accumulate(S,{},(s,c)=> s&{List.Transform(c,each Record.FieldOrDefault(Dict,_,_))}),

    t=Table.FromRows(Replacements,cols)
in
    t