Populate value in one column by dynamic value in another column

I made some assumptions

• The blank space next "$STUDENTANALYST" is  "Test User"
• In the "InsertionMessage" column, you want to replace ALL of the terms that start with "$" with the match in the "DynamicTags" Column.

If that is the case, the following code seems to work on your data sample:

let
    Source =Table.FromRecords({[InsertionMessage="$STUDENTANALYST moved $ACTIVITY to $STATUS", 
              DynamicTags="#(lf)" & """$STUDENTANALYST"":""Test User""" & "#(lf)" & """$STATUS"":""Under Review""" & "#(lf)" & "#(lf)" ]},
              type table[InsertionMessage=text, DynamicTags=text]),
    
    #"Replace With Tags" = Table.ReplaceValue(
        Source,
        each [InsertionMessage],
        each [a=Text.Split([DynamicTags],"#(lf)"),
              b=List.Transform(a, each Text.Split(_,":")),
              c=List.Select(b, each List.Count(_)=2),
              d=List.Transform(c, each List.Combine(List.Transform(_, (l)=>Text.Split(l,":")))),
              e=List.Transform(d, each List.Transform(_,(l)=>Text.Trim(l,"""")) )
        ][e],
        (x,y,z) as text => List.Accumulate(
                    z,
                    y,
                    (s,c)=>Text.Replace(s,c{0},c{1})),
                    {"InsertionMessage"})
in
    #"Replace With Tags"

The code generates a List of replacement values from the DynamicTags column, then uses List.Accumulate to perform the multiple replacements required.

Source data

Results:

If you only want to replace the first instance of $word, the fix is simple.

Hi patrickoleary85 , here's another solution that you could look at. Thanks!

Here, I have created two tables: 
1. ReplaceValue table stemming out of the DynamicTags column to find the replacement values for each word.

2. Solution table is where the ReplaceValue table values are referenced to get the new statement.
3. I'll leave the code for both the tables below.
ReplaceValue M Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][DynamicTags],
List = List.Transform(List.Combine(List.Transform(Source, each Text.Split(_,","))), each Text.Split(_,":")),
Split = Table.FromRows(List),
Replace = Table.TransformColumns(Split,{}, each Text.Replace(_,"""","")),
Trim = Table.TransformColumns(Replace,{{"Column1", Text.Trim, type text}})
in
Trim

Solution Table M Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"InsertionMessage", type text}, {"DynamicTags", type text}}),
List = Table.TransformColumns(#"Changed Type",{"InsertionMessage", each Text.Split(_," ")}),
Position = Table.TransformColumns(List,{"InsertionMessage", each List.Transform(_, each if List.PositionOf(ReplaceValue[Column1],_) >= 0 then List.PositionOf(ReplaceValue[Column1],_) else _)}),
Records = Table.TransformColumns(Position,{"InsertionMessage", each List.Transform(_, each try Record.ToList(Record.SelectFields(ReplaceValue{_},"Column2")){0} otherwise _)}),
Final = Table.TransformColumns(Records,{"InsertionMessage", each Text.Combine(_, " ")})
in
Final



 

Basically same pattern as what ronrsnfld's post already covered. Main tweak of the below is to parse DynamicTags with Json.Document rather than manually splitting on line breaks, etc.

let
    Source = SourceData,

    ReplaceDynamicTags = 
    Table.ReplaceValue(
        Source, null,
        //Get list of lists where inner list is field name,value pairing 
        //from original DynamicTags text
        each Table.ToRows( Record.ToTable( 
            Json.Document( "{" & [DynamicTags] & "}" ) 
        ) ),
        //Using custom replacer function to leverage List.Accumulate. 
        //dynTags refers to the JSON-parsed list of name,value pairs
        (origText, notUsed, dynTags) as text => 
        List.Accumulate( 
            dynTags, origText, 
            (state, current) => Replacer.ReplaceText( state, current{0}, current{1} ) 
        ),
        {"InsertionMessage"}
    )
in
    ReplaceDynamicTags