Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
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
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
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
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.
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!
Wow! that's awesome, you literally saved my day!
Thanks for sharing this.
That should be the ACCEPTED SOLUTION
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |