Loop through table and replace values within text from another table

OK, so adding a column like this should do the job:

List.Accumulate(List.Buffer(Table.ToRecords(Tags)), [HTML Column], (state,current)=> Text.Replace(state, "[pp_" & current[Tag] & "_pp]", current[Value]))

But for performance reasons make sure to buffer the Tags-table like in the file attached.

Brilliant 🙂
To do this, you have to merge the Tags table to your main table. Then you perform the operation on the partition that sits in the newly created column instead.
The code looks like so and the file is also attached.

// PA Data
let
    Source = Excel.CurrentWorkbook(){[Name="HTML"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Clause Text", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Child Var", {"ParentID"}, "Child Var", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Result", each List.Accumulate(List.Buffer(Table.ToRecords([Child Var])), [Clause Text], (state,current)=> Text.Replace(state, "[pp_" & current[VariableID] & "_pp]", current[VariableValue]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Child Var"})
in
    #"Removed Columns"

// Child Var
let
    Source = Excel.CurrentWorkbook(){[Name="Tags"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ParentID", type text}, {"VariableID", type text}, {"VariableValue", type text}})
in
    #"Changed Type"

Hi Melinda_Newbie ,
yes, there are multiple ways to do it with different performance implications.
How many rows does each of those tables have?

Hi Melinda_Newbie 

You can create two  blank query, then put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY+9bsMwDAZfpfCcgaRIihyN/MzdgwwyJbdBgXZol+bpKwMNoNGrcKfveL1OCDQdpioQbqs7sXARLI5Sm1dbgTxx7QjSdDs8+YZrXWrClcvC1roSrGqSmrWoyNuXX2+DgClpVc3ALfPiZFkEiNA1zEpeOsLyUsvv97/EO6tg4HdURfkZhB1VCcYq7S9wulzseFZGVJKzoZ0c0jwTk/Gs27Wv98ejDAqbB1GJoj3bKJcIEAbLTSERwYbA8xLbt3F8v8dH+xykHSu0rdz+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Parent ID" = _t, Tag = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent ID", Int64.Type}, {"Tag", type text}, {"Value", type text}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1Y87DsJADAWvglJT2F7ba5eBwCWiCO0voqDI/SsSJJBA9IjmdaOZN44dAnX77nBL191j8jbjslyqQHGb3YmFk2BylNq82gzkgetlWabdF67hXHMNOHPKbG1FC6uahGatVOSV+4ZhCFpVI3CLnJ0sigARuhazFPOmW0O7ab818x8267P5xcNwPtvxpIyoJCdDGxxC3xOTca/0kfsOs3khSiXp+swoplJAGCw2hUAE73L7kXy6Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"HTML Column" = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"HTML Column", type text}, {"Column1", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","[pp_","",Replacer.ReplaceText,{"HTML Column"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","_pp","",Replacer.ReplaceText,{"HTML Column"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","]","",Replacer.ReplaceText,{"HTML Column"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value2", "HTML Column", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"HTML Column.1", "HTML Column.2", "HTML Column.3", "HTML Column.4", "HTML Column.5", "HTML Column.6", "HTML Column.7", "HTML Column.8", "HTML Column.9", "HTML Column.10", "HTML Column.11", "HTML Column.12"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"HTML Column.1", type text}, {"HTML Column.2", type text}, {"HTML Column.3", type text}, {"HTML Column.4", type text}, {"HTML Column.5", type text}, {"HTML Column.6", type text}, {"HTML Column.7", type text}, {"HTML Column.8", type text}, {"HTML Column.9", type text}, {"HTML Column.10", type text}, {"HTML Column.11", type text}, {"HTML Column.12", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"ID", "Value"}, Query1, {"Parent ID", "Tag"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Value"}, {"Value.1"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Expanded Query1",each [Value.1],each if [Value.1]=null then [Value] else [Value.1],Replacer.ReplaceValue,{"Value.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value3",{"Value"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Index", Order.Ascending}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Attribute]), "Attribute", "Value.1"),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"HTML Column.1", "HTML Column.2", "HTML Column.3", "HTML Column.4", "HTML Column.5", "HTML Column.6", "HTML Column.7", "HTML Column.8", "HTML Column.9", "HTML Column.10", "HTML Column.11", "HTML Column.12"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Melinda_Newbie ,
it transforms the table "tags" into a list of records.

Did you see that my answer included a file as well?

This should make it easier for you to follow the solution, I hope.

No worries, we'll figure it out 🙂
When you say "The tags are already in a list" - what do you actually mean with that in regard to Power Query?
Can you share a screenshot or even a sample file maybe?

Ok, so your tag table is called "Child Var". 
Then you have to adjust the formula like so:

List.Accumulate(List.Buffer(Table.ToRecords(#"Child Var")), [Clause Text], (state,current)=> Text.Replace(state, "[pp_" & current[VariableID] & "_pp]", current[VariableValue]))