Forum Discussion
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...
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 replacedin 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
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
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
Wow! that's awesome, you literally saved my day!
Thanks for sharing this.
That should be the ACCEPTED SOLUTION
Excellent job!
You are the chump!
Thanks a lot you save me a lot of time!
Cheers!
