Forum Discussion

Melinda_Newbie's avatar
Melinda_Newbie
New Member
2 years ago
Solved

Loop through table and replace values within text from another table

I’m new to Power BI so pardon my question, but I’m wondering if there is a way to do the following: I have a SQL table with a column that is HTML that contains something like below.  Not all HTML is...
Need Help
  • ImkeF's avatar
    ImkeF
    2 years ago

    Hi Melinda_Newbie ,
    yes, this should be possible:

    // 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)=>  if current[VariableID] = null then state else Text.Replace(state, "[pp_" & current[VariableID] & "_pp]", current[VariableValue]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Child Var"})
in
    #"Removed Columns"
ImkeF's avatar
ImkeF
Community Champion
2 years ago

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.

Melinda_Newbie's avatar
Melinda_Newbie
New Member
2 years ago

Thank you for this, but I'm not able to get this to work.  Can you possibly explain what the tags represents in this portion?  

(Table.ToRecords(Tags))