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, there are multiple ways to do it with different performance implications.
How many rows does each of those tables have?

  • Melinda_Newbie's avatar
    Melinda_Newbie
    New Member
    2 years ago

    Depends on what query I'm running, but I typically have 3,000-4,000 rows of HTML data and then the tag table would all depend on how many tags are in the HTML.  These are reports I'm trying to develop so it all depends on what each report request includes.