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

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"
Melinda_Newbie's avatar
Melinda_Newbie
New Member
2 years ago

I did get this to work, but occasionally the VariableID is missing, which is causing errors.  Is there a way to state that if the variableID is not found then ignore the pp_xxx_pp?  Or replace it with "Blank"?  

 

 

VariableID