Loop through table and replace values within text from another table

[Melinda_Newbie]

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 the same, not all tags ([pp_xxxx_pp]) are the same.

Parent ID	HTML Column
102	Blah Blah blah [pp_d50c98f992454a51a915de9d8f02934d_pp] Blah Blah blah [pp_e1fdbd31f4ab48e4a5c466853e8ecd14_pp Blah Blah blah [pp_1336d66704e74b928755022196c88a7b_pp]
104	Blah Blah blah [pp_d50c98f992454a51a915de9d8f02934d_pp] Blah Blah blah [pp_e1fdbd31f4ab48e4a5c466853e8ecd14_pp Blah Blah blah [pp_1336d66704e74b928755022196c88a7b_pp]
106	Blah blah [pp_0DFF8CE6411625E818D903AA24284A62_pp] Blah Blah Blah blah [pp_489c22aca6934827acc054087e603220_pp]
108	Blah blah [pp_0DFF8CE6411625E818D903AA24284A62_pp] Blah Blah Blah blah [pp_489c22aca6934827acc054087e603220_pp]

I have another table that contains the unique values for the tags, that are only valid for that unique Parent ID

Parent ID	Tag	Value
102	d50c98f992454a51a915de9d8f02934d	$12.00
102	e1fdbd31f4ab48e4a5c466853e8ecd14	dog
102	1336d66704e74b928755022196c88a7b	45 days
104	d50c98f992454a51a915de9d8f02934d	$10.00
104	e1fdbd31f4ab48e4a5c466853e8ecd14	cat
104	1336d66704e74b928755022196c88a7b	30 days
106	0DFF8CE6411625E818D903AA24284A62	Pizza
106	489c22aca6934827acc054087e603220	$400.00
108	0DFF8CE6411625E818D903AA24284A62	Chicken
108	489c22aca6934827acc054087e603220	$200.00

I’ve exported both tables to Power BI.  Is there a way to loop through the HTML and replace the tag with the Value from the table?

Desired Outcome:

Parent ID	Final
102	Blah Blah blah 12.00 Blah Blah blah dog Blah Blah blah 45 days
104	Blah Blah blah 10.00 Blah Blah blah cat Blah Blah blah 30 days
106	Blah blah Pizza Blah Blah Blah blah $400.00
108	Blah blah Chicken Blah Blah Blah blah $200.00

[ImkeF · Accepted solution]

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]

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]

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

[ImkeF]

Ok, so your tag table is called "Child Var". 
Then you have to adjust the formula like so:

List.Accumulate(List.Buffer(Table.ToRecords(#"Child Var")), [Clause Text], (state,current)=> Text.Replace(state, "[pp_" & current[VariableID] & "_pp]", current[VariableValue]))

[Melinda_Newbie]

Oh wow, that almost worked!  It's populating everything but it's just populating with data from the first Parent ID.  I need it to match on the parent ID = ID and populate with that records values.

[ImkeF]

No worries, we'll figure it out 🙂
When you say "The tags are already in a list" - what do you actually mean with that in regard to Power Query?
Can you share a screenshot or even a sample file maybe?

[Melinda_Newbie]

The tag values are in another query in the power editor.  

Parent ID           Tag                                              Value

102	d50c98f992454a51a915de9d8f02934d	$12.00
102	e1fdbd31f4ab48e4a5c466853e8ecd14	dog
102	1336d66704e74b928755022196c88a7b	45 days
104	d50c98f992454a51a915de9d8f02934d	$10.00
104	e1fdbd31f4ab48e4a5c466853e8ecd14	cat
104	1336d66704e74b928755022196c88a7b	30 days
106	0DFF8CE6411625E818D903AA24284A62	Pizza
106	489c22aca6934827acc054087e603220	$400.00
108	0DFF8CE6411625E818D903AA24284A62	Chicken
108	489c22aca6934827acc054087e603220	$200.00

The highlighted field from the PA Data needs to join to the Child Var data and replace the pp_xx_pp with the value.

2.2.3 Inpatient Outlier.  When the length of stay exceeds [pp_d50c98f992454a51a915de9d8f02934d_pp] days during a single Admission ("Inpatient Outlier Threshold"), the contract rate will be a Per Diem of $[pp_e1fdbd31f4ab48e4a5c466853e8ecd14_pp] for each day in excess of the Inpatient Outlier Threshold in addition to the applicable contract rate set forth in section 2.2. This section 2.2.3 applies to all inpatient service categories except any service for which the contract rate is zero, Rehabilitation, Hospice, Inpatient Skilled Nursing Services [pp_1336d66704e74b928755022196c88a7b_pp]

Result:

2.2.3 Inpatient Outlier.  When the length of stay exceeds thirty(30) days during a single Admission ("Inpatient Outlier Threshold"), the contract rate will be a Per Diem of $8291 for each day in excess of the Inpatient Outlier Threshold in addition to the applicable contract rate set forth in section 2.2. This section 2.2.3 applies to all inpatient service categories except any service for which the contract rate is zero, Rehabilitation, Hospice, Inpatient Skilled Nursing Services, Nursery and Obstetrics

[ImkeF]

Hi @Melinda_Newbie ,
it transforms the table "tags" into a list of records.

Did you see that my answer included a file as well?

This should make it easier for you to follow the solution, I hope.

[Melinda_Newbie]

Yes, I saw the attachment, but have no idea what you are doing there.  The tags are already in a list, so I'm not sure why or how you would need to create another one?  Sorry, I know I've got a lot to learn.

[v-xinruzhu-msft]

Hi @Melinda_Newbie 

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.

[Melinda_Newbie]

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.

[ImkeF]

OK, so adding a column like this should do the job:

List.Accumulate(List.Buffer(Table.ToRecords(Tags)), [HTML Column], (state,current)=> Text.Replace(state, "[pp_" & current[Tag] & "_pp]", current[Value]))

But for performance reasons make sure to buffer the Tags-table like in the file attached.

[Melinda_Newbie]

Thank you for this, but I'm not able to get this to work.  Can you possibly explain what the tags represents in this portion?  

(Table.ToRecords(Tags))

[ImkeF]

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]

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.