Forum Discussion
Replacing characters in a string based on another column
You used the wrong syntax.
each [field name]not
each "field name"
Look at my code sample above.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
Use each [Cycle Name], like below
#"Replaced Value" =
Table.ReplaceValue(
#"Changed Type",
99,
each [Result],
Replacer.ReplaceValue,
{"Value"}
)
Here is full code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYlOlWJ1opSQgyxiIzcC8ZCDL0hJImIO5KVClFkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Value = _t, Result = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Result", Int64.Type}}),
#"Replaced Value" =
Table.ReplaceValue(
#"Changed Type",
99,
each [Result],
Replacer.ReplaceValue,
{"Value"}
)
in
#"Replaced Value"
It replaced the 99 in the highlighted cell with the 7 from the Result column.
Using each I got this. It replaced the "99" with the column name rather than the value in that column.
= Table.ReplaceValue(#"Added Conditional Column2","99",each "calc-What cycle is the group in?",Replacer.ReplaceText,{"What cycle is the group in?"})
Is there a way to tell it to put the numbers in that column as the replacement string or is this the wrong way to approach the problem?
You used the wrong syntax.
each [field name]not
each "field name"
Look at my code sample above.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.Thanks! I was able to get it to work.
I changed the column name from "calc-What cycle is the group in?" to "cycle" because the column name format was throwing errors when enclosed in brackets.
= Table.ReplaceValue(#"Renamed Columns1","99",each [cycle],Replacer.ReplaceText,{"What cycle is the group in?"})