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...
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"
You can create two blank query, then put the following code to advanced editor in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY+9bsMwDAZfpfCcgaRIihyN/MzdgwwyJbdBgXZol+bpKwMNoNGrcKfveL1OCDQdpioQbqs7sXARLI5Sm1dbgTxx7QjSdDs8+YZrXWrClcvC1roSrGqSmrWoyNuXX2+DgClpVc3ALfPiZFkEiNA1zEpeOsLyUsvv97/EO6tg4HdURfkZhB1VCcYq7S9wulzseFZGVJKzoZ0c0jwTk/Gs27Wv98ejDAqbB1GJoj3bKJcIEAbLTSERwYbA8xLbt3F8v8dH+xykHSu0rdz+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Parent ID" = _t, Tag = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent ID", Int64.Type}, {"Tag", type text}, {"Value", type text}})
in
#"Changed Type"let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1Y87DsJADAWvglJT2F7ba5eBwCWiCO0voqDI/SsSJJBA9IjmdaOZN44dAnX77nBL191j8jbjslyqQHGb3YmFk2BylNq82gzkgetlWabdF67hXHMNOHPKbG1FC6uahGatVOSV+4ZhCFpVI3CLnJ0sigARuhazFPOmW0O7ab818x8267P5xcNwPtvxpIyoJCdDGxxC3xOTca/0kfsOs3khSiXp+swoplJAGCw2hUAE73L7kXy6Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"HTML Column" = _t, Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"HTML Column", type text}, {"Column1", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","[pp_","",Replacer.ReplaceText,{"HTML Column"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","_pp","",Replacer.ReplaceText,{"HTML Column"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","]","",Replacer.ReplaceText,{"HTML Column"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value2", "HTML Column", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"HTML Column.1", "HTML Column.2", "HTML Column.3", "HTML Column.4", "HTML Column.5", "HTML Column.6", "HTML Column.7", "HTML Column.8", "HTML Column.9", "HTML Column.10", "HTML Column.11", "HTML Column.12"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"HTML Column.1", type text}, {"HTML Column.2", type text}, {"HTML Column.3", type text}, {"HTML Column.4", type text}, {"HTML Column.5", type text}, {"HTML Column.6", type text}, {"HTML Column.7", type text}, {"HTML Column.8", type text}, {"HTML Column.9", type text}, {"HTML Column.10", type text}, {"HTML Column.11", type text}, {"HTML Column.12", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"ID", "Value"}, Query1, {"Parent ID", "Tag"}, "Query1", JoinKind.LeftOuter),
#"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Value"}, {"Value.1"}),
#"Replaced Value3" = Table.ReplaceValue(#"Expanded Query1",each [Value.1],each if [Value.1]=null then [Value] else [Value.1],Replacer.ReplaceValue,{"Value.1"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value3",{"Value"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Index", Order.Ascending}}),
#"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Attribute]), "Attribute", "Value.1"),
#"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"HTML Column.1", "HTML Column.2", "HTML Column.3", "HTML Column.4", "HTML Column.5", "HTML Column.6", "HTML Column.7", "HTML Column.8", "HTML Column.9", "HTML Column.10", "HTML Column.11", "HTML Column.12"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
#"Merged Columns"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your response. I've thought about splitting the columns by delimiters, but I just supplied an easy version in the example. Sometimes the HTML column is quite long and has many, many tags.
