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

[Richard_Halsall]

Hi,

I am asking for help to see if there is a better solution to the my current code

I am needing to replace a value in a column with a value in another column based on a condition but across multiple columns in a single step - I am not sure if this is possible - but I have 20 'pairs' of columns I need to do this for

My simplified sample code below is written for just 3 'pairs' of columns so has 3 steps but I would like to do it in 1 as they all reference the same 'condition' column

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}}),
ReplacedWorking = Table.ReplaceValue(ChangedType,
each [Working_Amount__c],
each if [AllocationId] <> null or [AllocationId] = "" then [Working_Amount_s__c]
else [Working_Amount__c],
Replacer.ReplaceValue,{"Working_Amount__c"}),
ReplacedOnsite = Table.ReplaceValue(ReplacedWorking,
each [Standby_Onsite_Amount__c],
each if [AllocationId] <> null or [AllocationId] = "" then [Onsite_Standby_Amount_s__c]
else [Standby_Onsite_Amount__c],
Replacer.ReplaceValue,{"Standby_Onsite_Amount__c"}),
ReplacedOffsite = Table.ReplaceValue(ReplacedOnsite,
each [Standby_Offsite_Amount__c],
each if [AllocationId] <> null or [AllocationId] = "" then [Offsite_Standby_Amount_s__c]
else [Standby_Offsite_Amount__c],
Replacer.ReplaceValue,{"Standby_Offsite_Amount__c"})
in
ReplacedOffsite

Thank you in advance

[PwerQueryKees]

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

[Omid_Motamedise]

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

[Richard_Halsall]

Hi @Omid_Motamedise , thanks for the reply unfortunately the values are changing even if the allocation id column is blank or null, they are to change only if they are populated.

[PwerQueryKees · Accepted solution]

Try code below. I used the Table.TransformRows() to loop through all rows in the table and if the AllocationId of the row is <> null and "" ,  I use List.Accumulate() to do a Record.TransformFields, for each pair. 

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"}
    },
    UpdatedTable = Table.FromRecords(
        Table.TransformRows(ChangedType,
            (row) =>
                if (row[AllocationId] ?? "") <> "" then
                    List.Accumulate(
                        ColumnPairs,
                        row,
                        (State, CurrentPair) =>
                            Record.TransformFields(State, {CurrentPair{0}, each Record.Field(State, CurrentPair{1})})
                    )
                else
                    row
        )
    )
in
    UpdatedTable

[FarhanJeelani]

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.

[Richard_Halsall]

Hi @FarhanJeelani thanks for helping with the code but it is throwing an error in the target columns e.g. 

Working_Amount__c

of the form 

Can you help to resolve? Thanks