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
Excellent job!
You are the chump!
Thanks a lot you save me a lot of time!
Cheers!