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

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"