cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Replace multiple values in multiple columns in one step

Hi,

 

i have a table like that:image.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
Memorable Member
Memorable Member

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

 

 

 

View solution in original post

8 REPLIES 8
Super User II
Super User II

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.

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

 

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

 

 

 

Memorable Member
Memorable Member

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.

 

image.png

 

 

 

image.png

 

 

 

Solution Specialist
Solution Specialist

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:

image.png

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
    TableReplaceValuesFromTableFunction = (table as table, transformationTable as table, optional columns as any, optional specifyTransformationColumns as list, optional missingField as nullable number) as table =>
        
        let
            // Buffer Transformation Table to call it only once
            BufferedTransformationTable = Table.Buffer(transformationTable),
            // Step to add error handling for given table & columns
            tryTransformationColumnNames = try
                let
                    temp = Table.ColumnNames(BufferedTransformationTable),
                    countTargetColumns = List.Count(temp),
                    countSpecifiedColumns = try List.Count(specifyTransformationColumns) otherwise 0
                in
                    if countTargetColumns < 2 then error [Reason = "", Message = "The transformation table needs to have at least two columns", Detail = [TransformationColumns = temp] ]
                    else if countSpecifiedColumns = 0 then {temp{0}, temp{1}}
                    else if countSpecifiedColumns = 2 then
                        let
                            l = List.Intersect({specifyTransformationColumns, temp})
                        in
                            if List.Count(l) = 2 then l else error  [Reason = "", Message = "The transformation table does not contain the 2 specified columns", Detail = [TransformationColumns = temp, SpecifiedColumns = specifyTransformationColumns] ]
                    else error [Reason = "Error in specified transformation columns", Message = "Please specify exactly 2 columns", Detail = [SpecifiedColumns = specifyTransformationColumns ]],
            TransformationColumnNames = tryTransformationColumnNames[Value],

            // Define Replacer Function to trick Table.ReplaceValue
            CustomReplacer =
                (value as any, old as any, new as any) as any =>
                    let
                        getvalue = try
                                Expression.Evaluate(
                                    "tbl{["
                                    & TransformationColumnNames{0}
                                    & " = t]}["
                                    & TransformationColumnNames{1}
                                    & "]",
                                    [ tbl = BufferedTransformationTable, t = value ]
                                ),
                        newvalue = 
                            if getvalue[HasError] then 
                                if Text.Contains(getvalue[Error][Message], "The key matched more than one row in the table") then error [Reason = "An error occured in the Transformation Table", Message = "More than one replacements were found for the specified value", Detail = [ValueToReplace = value]]
                                else if (missingField??1) = 0 then
                                    if Text.Contains(getvalue[Error][Message], "The key didn't match any rows in the table") then error [Reason = "An error occured in the Transformation Table", Message = "A replacement cannot be found for the specified value", Detail = [ValueToReplace = value]]
                                    else error getvalue[Error]
                                else if (missingField??1) = 2 then null
                                else value
                            else getvalue[Value]
                    in
                        newvalue,

            // If table and columns ok, call Table.ReplaceValue
            Result = if tryTransformationColumnNames[HasError] then error tryTransformationColumnNames[Error] else Table.ReplaceValue( table, null, null, CustomReplacer, columns??Table.ColumnNames(table) )
        in
            Result,



    // Add documentation
    TableReplaceValuesFromTableType = type function (
                table as (type table meta [
                    Documentation.FieldCaption ="Target Table",
                    Documentation.FieldDescription = "The table to make replacements"
                    ]),
                transformationTable as (type table meta [
                    Documentation.FieldCaption ="Transformation Table",
                    Documentation.FieldDescription = "The lookup table for replacements"
                    ]),
                optional columns as (type any meta [
                    Documentation.FieldCaption ="Columns",
                    Documentation.FieldDescription = "The columns in which to replace values"
                    ]),
                optional specifyTransformationColumns as (type {text} meta [
                    Documentation.FieldCaption ="Transformation Columns",
                    Documentation.FieldDescription = "Columns to check on Transformation Table. If not specified, Column1 and Column2 are used."
                    ]),
                optional missingField as MissingField.Type
                )
        as table meta
            [Documentation.Name = "Table.ReplaceValuesFromTable",
            Documentation.Description = "Replaces multiple values at once using a lookup table",
            Documentation.LongDescription = "Replaces multiple values at once using a lookup table.#(lf)If no columns are specified for the Transformation Table, the first column is used a a lookup and the second as a replacement. If no columns are specified in the Target Table then the default is all columns",
            Documentation.Category = "Table",
            Documentation.Author = "Spyros Mavroforos",
            Documentation.Examples = {[
                    Description = "Replace values on all columns",
                    Code = "let
    StartingTable =
        Table.FromRecords({
            [a = 1, b = 2],
            [a = 2, b = 2],
            [a = 3, b = 4],
            [a = 4, b = 5]
        }),

    transformationTable = 
        Table.FromRecords({
            [check = 1, replace = ""A""],
            [check = 2, replace = ""B""],
            [check = 3, replace = ""C""],
            [check = 4, replace = ""D""]
        })
in
    Table.ReplaceValuesFromTable(StartingTable, transformationTable)",
                Result = "Table.FromRecords({
            [a = ""A"", b = ""B""],
            [a = ""B"", b = ""B""],
            [a = ""C"", b = ""D""],
            [a = ""D"", b = 5]
        })"
                ],
                [
                    Description = "Replace values on column 'a'",
                    Code = "let
    StartingTable =
        Table.FromRecords({
            [a = 1, b = 2],
            [a = 2, b = 2],
            [a = 3, b = 4],
            [a = 4, b = 5]
        }),

    transformationTable = 
        Table.FromRecords({
            [check = 1, replace = ""A""],
            [check = 2, replace = ""B""],
            [check = 3, replace = ""C""],
            [check = 4, replace = ""D""]
        })
in
    Table.ReplaceValuesFromTable(StartingTable, transformationTable, {""a""})",
                Result = "Table.FromRecords({
            [a = ""A"", b = 2],
            [a = ""B"", b = 2],
            [a = ""C"", b = 3],
            [a = ""D"", b = 5]
        })"
                ],
                [
                    Description = "Replace values on column 'b' by using 'Column2' column as lookup and 'Column1' as replacement",
                    Code = "let
    StartingTable =
        Table.FromRecords({
            [a = ""A"", b = ""B""],
            [a = ""B"", b = ""B""],
            [a = ""C"", b = ""D""],
            [a = ""D"", b = 5]
        }),

    transformationTable = 
        Table.FromRecords({
            [Column1 = 1, Column2 = ""A""],
            [Column1 = 2, Column2 = ""B""],
            [Column1 = 3, Column2 = ""C""],
            [Column1 = 4, Column2 = ""D""]
        })
in
    Table.ReplaceValuesFromTable(StartingTable, transformationTable, {""b""}, {""replace"", ""check""}, MissingField.Error)",
                Result = "Table.FromRecords({
            [a = ""A"", b = 2],
            [a = ""B"", b = 2],
            [a = ""C"", b = 4],
            [a = ""D"", b = error [Reason = """", Message = ""A replacement cannot be found for the specified value"", Detail = [ValueToReplace = 5]]
        })"
                ]}
        ]

in
    Value.ReplaceType(TableReplaceValuesFromTableFunction, TableReplaceValuesFromTableType)

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




Feel free to connect with me:
LinkedIn

Memorable Member
Memorable Member

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

 

 

 

View solution in original post

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

 

 

 

 

 

 

Community Support
Community Support

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors