Forum Discussion
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...
ImkeF
Community Champion
Community ChampionBrilliant 🙂
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"
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