Forum Discussion
Loop through table and replace values within text from another table
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"
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?
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
