Replace multiple values in multiple columns in one step

[Syndicate_Admin]

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 there a way to do the replace with this multiple values in multiple custom selected columns?

i only found solutions for multiple values for one column... or one value for multiple columns.

Thanks for your answers

[Syndicate_Admin]

There was a suggestion the other day to unpivot the data into key/value pairs, do the substitutions, and then re-pivot.  I thought that was a pretty neat idea.

[Syndicate_Admin]

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"}
)

[Syndicate_Admin]

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

[Syndicate_Admin]

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.

[Syndicate_Admin]

Hi @elaj ,

I've had too much free time this morning, so I created a function to replace values based on a Transformation Table.

What's good, is that it calls the Target Table once and the Transformation Table once, using a custom Replacer Function inside Table.ReplaceValue.

By too much free time, I mean that not only I've added error handling, but documentation as well.

1) You're going to need a Transformation Table. It should be something that looks like that:

Column names are irrelevant, but the lookup column should be distinct for obvious reasons. If no lookup column is specified, the first column is used as a lookup column and the second as a replacement.

Let's say you name this Transformations

2) Adding the function as a new query. I'm gonna use a spoiler tag here cause it's too long.

Spoiler

Let's say you name the query TableReplaceValuesFromTable

3) Changing your actual table. Supposing PreviousStep is your previous step, you can now add a step calling the function:

= TableReplaceValuesFromTable(PreviousStep, Transformations)

If you need to specify the columns, I hope you can find out how.

Best,

Spyros

[Syndicate_Admin · Accepted solution]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

Hi @elaj 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[Syndicate_Admin]

Wow! that's awesome, you literally saved my day!

Thanks for sharing this.

That should be the ACCEPTED SOLUTION