multiple-columns powerquery multiple-conditions

Power Query Editor - Edit multiple columns with different values based on multiple conditions

I am using Power Query Editor and need to change several columns based on a several conditions.

At the moment I am doing this in 3 separate steps using Table.ReplaceValue, but with each iterative step the variables in the conditions will change (because the first step is CommentCOPY = Comment, and I am relying on the other actions only happening when CommentCOPY = ""), therefore I need to do all 3 actions based on the first state (or to put it another way, 3 actions based on a single set of conditions).

I also only want the entire thing to run if a certain condition is true.

In my mind I want to do something like:

if Scope = "A" then
if ActionNeeded ^="" and CommentCOPY ="" then do;
   CommentCOPY = Comment;
   Action = ActionNeeded;
   AssignedTo = "Manager";
   end;
;

But I need power query code. Here is the initial table:

AssignedTo	ActionNeeded	Comment	Scope
User1	1	Do it now	A
User1	2	Do it soon	A
User2		Do it soon	A
User2	3	Do it whenever	A
User3	1	Do it now	A
User3	2	Do it soon

And the result:

AssignedTo	CommentCOPY	Action	ActionNeeded	Comment	Scope
Manager	Do it now	1	1	Do it now	A
Manager	Do it soon	2	2	Do it soon	A
User2				Do it soon	A
Manager	Do it whenever	3	3	Do it whenever	A
Manager	Do it now	1	1	Do it now	A
User3			2	Do it soon

Is power query capable of this kind of thing?

Solution

You can transform multiple columns at once by using this method to do the transformations row by row.

Change the Source line to reflect your actual data source.
Note that I set the data types for the null columns from any to the possible eventual type.
Also note that in your pasted table, what you referred to as "" were actually null's. In PQ there is a difference. Depending on what your actual data looks like, you may need to change these comparisons in the code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table41"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"AssignedTo", type text}, {"CommentCOPY", type text}, {"Action", Int64.Type}, 
        {"ActionNeeded", Int64.Type}, {"Comment", type text}, {"Scope", type text}}),

    #"Transform Multiple Columns" = 
        Table.FromRecords(
            Table.TransformRows(#"Changed Type",
                (r) => if r[Scope] = "A" and r[ActionNeeded] <> null and r[CommentCOPY] = null 
                    then
                Record.TransformFields(
                        r,
                        {
                            {"CommentCOPY", each r[Comment]},
                            {"Action", each r[ActionNeeded]},
                            {"AssignedTo", each "Manager"}
                        }
                    )
                    else r
                ),
                Value.Type(#"Changed Type")
            )
in
    #"Transform Multiple Columns"