Replacing characters in a string based on another column

[Anonymous]

I'm sure this is just a syntax issue but I'm trying to replace a value in a string with another column. I

n this case I want the "99" in "Other (99)" to be the value of another column, "Cycle number." I just don't know how to reference the column in the code.

= Table.ReplaceValue(#"Added Conditional Column2","99","Cycle number",Replacer.ReplaceText,{"What cycle is the group in?"})

This replaces the text with the quoted string but I want the values in the column of the same name.

Thanks!

Thanks

[edhans]

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. 

[Anonymous]

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? 

[edhans · Accepted solution]

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.

[Anonymous]

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?"})

[edhans]

Yeah, when you have special characters in a field, you have to do it like this, which is a pain:

[#"calc-What cycle is the group in?"]