Replace values in one column with another column value with condition across multiple columns

Hi Richard_Halsall ,

Your current approach works but can become unwieldy as the number of column pairs increases. To handle this more efficiently in Power Query, you can loop through the column pairs dynamically using a list of column names and a single List.Accumulate or Table.TransformColumns step. Here's a more scalable solution:

Optimized Solution:

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "lZNBC4IwFID/y84e3JxixyVqUcNWkal4UCSDOnTp/6dNYW6z3OANBu/7eG97KwpQuRFz7O8K43tDSQwsAO1uGwOJB1BaEyaC+5wSMaMP9xfRsoNKwJVwqOyAZ+MgSesXJRtVkg0SiEULMrRc1moppo4KUhJIDmzm8NLr0I0r3wsysGSR2s3EuqQd9qRkJ7/IGP5CyRFxia4ab5EjTNiNkqTLddCMSIO0HMG6dC1yPr05oozPPNI8+C/RPpge6edMnAn/H5FvJcJTiPID",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let _t = ((type nullable text) meta [Serialized.Text = true]) 
        in type table [
            Id = _t, 
            Working_Amount__c = _t, 
            Standby_Onsite_Amount__c = _t, 
            Standby_Offsite_Amount__c = _t, 
            Working_Amount_s__c = _t, 
            Onsite_Standby_Amount_s__c = _t, 
            Offsite_Standby_Amount_s__c = _t, 
            AllocationId = _t
        ]
    ),
    ChangedType = Table.TransformColumnTypes(
        Source,
        {
            {"Id", type text}, 
            {"Working_Amount__c", Int64.Type}, 
            {"Standby_Onsite_Amount__c", Int64.Type}, 
            {"Standby_Offsite_Amount__c", Int64.Type}, 
            {"Working_Amount_s__c", Int64.Type}, 
            {"Onsite_Standby_Amount_s__c", Int64.Type}, 
            {"Offsite_Standby_Amount_s__c", Int64.Type}
        }
    ),
    // Define the column pairs for replacement
    ColumnPairs = {
        {"Working_Amount__c", "Working_Amount_s__c"},
        {"Standby_Onsite_Amount__c", "Onsite_Standby_Amount_s__c"},
        {"Standby_Offsite_Amount__c", "Offsite_Standby_Amount_s__c"}
    },
    // Loop through the column pairs and replace values dynamically
    UpdatedTable = List.Accumulate(
        ColumnPairs,
        ChangedType,
        (State, CurrentPair) =>
            Table.TransformColumns(
                State,
                {
                    {CurrentPair{0}, 
                     each if [AllocationId] <> null and [AllocationId] <> "" 
                          then Record.Field(_, CurrentPair{1}) 
                          else _, 
                     type nullable number}
                }
            )
    )
in
    UpdatedTable

Explanation:

1. ColumnPairs Definition:

   • A list of pairs where each pair contains the name of the target column and the source column for replacement.
   • Example: {"Working_Amount__c", "Working_Amount_s__c"}.

2. List.Accumulate for Iterative Updates:

   • Starts with the ChangedType table.
   • Iteratively applies the transformation for each column pair using Table.TransformColumns.

3. Table.TransformColumns:

   • Dynamically updates the value in the target column based on the condition:
     • If AllocationId is not null or empty, it replaces the value with the corresponding value from the source column.
     • Otherwise, it keeps the original value.

4. Dynamic Handling:

   • The code will scale to any number of column pairs as defined in the ColumnPairs list.

Example Output:

For your data, this method produces the same results as your original code, but in a single efficient step. You can extend it to all 20 column pairs without adding redundant code.

Please mark this as solution if it helps. Appreciate Kudos.

You can also use this

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZNBC4IwFID/y84e3JxixyVqUcNWkal4UCSDOnTp/6dNYW6z3OANBu/7eG97KwpQuRFz7O8K43tDSQwsAO1uGwOJB1BaEyaC+5wSMaMP9xfRsoNKwJVwqOyAZ+MgSesXJRtVkg0SiEULMrRc1moppo4KUhJIDmzm8NLr0I0r3wsysGSR2s3EuqQd9qRkJ7/IGP5CyRFxia4ab5EjTNiNkqTLddCMSIO0HMG6dC1yPr05oozPPNI8+C/RPpge6edMnAn/H5FvJcJTiPID", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Working_Amount__c = _t, Standby_Onsite_Amount__c = _t, Standby_Offsite_Amount__c = _t, Working_Amount_s__c = _t, Onsite_Standby_Amount_s__c = _t, Offsite_Standby_Amount_s__c = _t, AllocationId = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Working_Amount__c", Int64.Type}, {"Standby_Onsite_Amount__c", Int64.Type}, {"Standby_Offsite_Amount__c", Int64.Type}, {"Working_Amount_s__c", Int64.Type}, {"Onsite_Standby_Amount_s__c", Int64.Type}, {"Offsite_Standby_Amount_s__c", Int64.Type}}),

ColumnPairs = {
{"Working_Amount__c", "Working_Amount_s__c"},
{"Standby_Onsite_Amount__c", "Onsite_Standby_Amount_s__c"},
{"Standby_Offsite_Amount__c", "Offsite_Standby_Amount_s__c"}},
X=List.Accumulate(ColumnPairs,ChangedType,(a,b)=>Table.ReplaceValue(a,
each Record.Field(_,b{0}),
each if [AllocationId] <> null or [AllocationId] = "" then Record.Field(_,b{1})
else Record.Field(_,b{0}),
Replacer.ReplaceValue,{b{0}}))

in
X

FarhanJeelani , Omid_Motamedise , you solutions seem to expect that the transformation function is passed the row in the record as parameter. I can not find this behaviour anywhere in the documentation and I always understood that the transformation function is passed a value.
Can you point me in the diretion of the documenation explaining the behaviour you seem to be expecting? I would LOVE it if TranformColumns could behave this way! It would be a game changer!