Populate value in one column by dynamic value in another column

[patrickoleary85]

Hi,

I am trying to figure out how to populate a dynamic value in one column with a value in another column. This is one example. Basically, I am looking to populate the value '$STUDENTANALYST' in the InsertionMessage column with the value from the "$STUDENTANALYST": "Test User" in the DynamicTags column. I tried splitting the DynamicTags column and then replacing the values in the table, but it doesn't seem to give me the flexibility I need. 

There are OTHER messages in the InsertionMessage column that are different, but also have DynamicTags. So the values in the InsertionMessage colum and DynamicTags column are not always this specific example. 

Instead of the output:

$STUDENTANALYST moved $ACTIVITY to $STATUS

I would get:

Test User moved $ACTIVITY to $STATUS

[ronrsnfld · Accepted solution]

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.

[SundarRaj · Accepted solution]

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



 

[v-ssriganesh]

Hi @patrickoleary85,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @patrickoleary85,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @patrickoleary85,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @MarkLaf, @SundarRaj & @ronrsnfld for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[MarkLaf · Accepted solution]

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

[SundarRaj · Accepted solution]

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



 

[ronrsnfld · Accepted solution]

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.