Forum Discussion

elaj's avatar
elaj
Helper 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

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

    This version is  a sort of hack wich does not use iterations. It only uses the function Table.ReplaceValue, using as fourth parameter (the place of function replacer)  the function Record.FieldOrDefault modified only for the order of its parameters

     

     

     

     

     

    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=[1="uno",3="tre",11="undici"],
replaced=Table.ReplaceValue(
    Source,
    Dict,
    "UnValoreQualsiasi",
    (x,y,z)=>Record.FieldOrDefault(y,x,x),
    cols
)
in
    replaced