Forum Discussion
Kenneth_Sarver
4 years agoNew Member
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 - .0...
- 4 years ago
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
ImkeF
Community Champion
4 years agoHi 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
StitchTogether
General 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