Forum Discussion
Find Column Name in String and Replace with Record Field from that Column Name
I am trying to dynamically replace each column name found in [Formula] with the value from that column in a single replace step.
Sample Source:
| Formula | A | B | C | |||
| .9 * A + .1 * B - .09 * C | PRODUCT | ETHANOL | RINS |
Desired Output:
| Formula | A | B | C | |||
| .9 * PRODUCT + .1 * ETHANOL - .09 * RINS | PRODUCT | ETHANOL | RINS |
Code for Finding A within [Formula] and replacing it with "PRODUCT":
ReplaceValue = Table.ReplaceValue(#"Changed Type",Table.ColumnNames(#"Changed Type"){1},each Record.Field(_, Table.ColumnNames(Source){1}),Replacer.ReplaceText,{"Formula"})
Hi Kenneth_Sarver ,
you can do that in one step like so:let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45W0rNU0FJwVNBW0DMEMpwUdBX0DEBCzko6SgFB/i6hziFAlmuIh6Ofvw+QFeTpF6wUGwsA", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Formula = _t, A = _t, B = _t, C = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Formula", type text}, {"A", type text}, {"B", type text}, {"C", type text}} ), StitchTogether = Table.AddColumn( #"Changed Type", "Result", each Text.Combine( List.ReplaceMatchingItems( Text.Split([Formula], " "), List.Skip(Table.ToRows(Record.ToTable(_))) ), "" ) ) in StitchTogetherGeneral method for this is described here: Multiple replacements or translations in Power BI and Power Query – The BIccountant
If you want to follow allong and understand what's going on in the formula, please paste this code into the advanced editor and follow the steps:let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45W0rNU0FJwVNBW0DMEMpwUdBX0DEBCzko6SgFB/i6hziFAlmuIh6Ofvw+QFeTpF6wUGwsA", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Formula = _t, A = _t, B = _t, C = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Formula", type text}, {"A", type text}, {"B", type text}, {"C", type text}} ), ReplacementsList = Table.AddColumn( #"Changed Type", "RelevantColumns", each List.Skip(Table.ToRows(Record.ToTable(_))) ), FormulaToList = Table.AddColumn( ReplacementsList, "SplitFormulaToList", each Text.Split([Formula], " ") ), Replacement = Table.AddColumn( FormulaToList, "Replacement", each List.ReplaceMatchingItems([SplitFormulaToList], [RelevantColumns]) ), StitchTogether = Table.AddColumn(Replacement, "Result", each Text.Combine([Replacement], "")) in StitchTogether
1 Reply
- ImkeF
Community Champion
Hi Kenneth_Sarver ,
you can do that in one step like so:let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45W0rNU0FJwVNBW0DMEMpwUdBX0DEBCzko6SgFB/i6hziFAlmuIh6Ofvw+QFeTpF6wUGwsA", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Formula = _t, A = _t, B = _t, C = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Formula", type text}, {"A", type text}, {"B", type text}, {"C", type text}} ), StitchTogether = Table.AddColumn( #"Changed Type", "Result", each Text.Combine( List.ReplaceMatchingItems( Text.Split([Formula], " "), List.Skip(Table.ToRows(Record.ToTable(_))) ), "" ) ) in StitchTogetherGeneral method for this is described here: Multiple replacements or translations in Power BI and Power Query – The BIccountant
If you want to follow allong and understand what's going on in the formula, please paste this code into the advanced editor and follow the steps:let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45W0rNU0FJwVNBW0DMEMpwUdBX0DEBCzko6SgFB/i6hziFAlmuIh6Ofvw+QFeTpF6wUGwsA", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Formula = _t, A = _t, B = _t, C = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Formula", type text}, {"A", type text}, {"B", type text}, {"C", type text}} ), ReplacementsList = Table.AddColumn( #"Changed Type", "RelevantColumns", each List.Skip(Table.ToRows(Record.ToTable(_))) ), FormulaToList = Table.AddColumn( ReplacementsList, "SplitFormulaToList", each Text.Split([Formula], " ") ), Replacement = Table.AddColumn( FormulaToList, "Replacement", each List.ReplaceMatchingItems([SplitFormulaToList], [RelevantColumns]) ), StitchTogether = Table.AddColumn(Replacement, "Result", each Text.Combine([Replacement], "")) in StitchTogether