Forum Discussion

Kenneth_Sarver's avatar
Kenneth_Sarver
New Member
4 years ago
Solved

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
        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

     

1 Reply

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity 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
        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