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