Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
elaj
Helper IV
Helper IV

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
Anonymous
Not applicable

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

11 REPLIES 11
Rickmaurinus
Helper V
Helper V

Hi there,

 

Replacing values in multiple columns is not the easiest task. One way is to use unpivot and to replace items using List.ReplaceMatchingItems. You can then reference the TranslationTable by combining List.ReplaceMatchingItems with List.Zip. I write about all this right here: 

 

https://gorilla.bi/power-query/replace-values/

 

Hope that helps!

 

Rick

 

--------------------------------------------------

@ me in replies or I'll lose your thread

 

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

v-juanli-msft
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

Anonymous
Not applicable

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
Not applicable

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

 

 

 

 

 

 

Smauro
Solution Sage
Solution Sage

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

Excellent job! 

You are the chump! 

Thanks a lot you save me a lot of time! 

 

Cheers!

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

 

Thanks for sharing this.

 

That should be the ACCEPTED SOLUTION

Anonymous
Not applicable

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

 

 

 

lbendlin
Super User
Super User

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.

Anonymous
Not applicable

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
Not applicable

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

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors