Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Replacing characters in a string based on another column

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

Need Help
  • edhans's avatar
    edhans
    4 years ago

    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.

5 Replies

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        edhans
        Community Champion
        4 years ago

        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.