PQ Column Transformation Based on Other Column

[cr731]

In Power Query, I would like to perform transformations on certain columns that are dependent on the value in another column. For example, in this table,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Value
[/TD]
[TD]Col1
[/TD]
[TD]Col2
[/TD]
[TD]Col3
[/TD]
[TD]Col4
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]aaa
[/TD]
[TD]123
[/TD]
[TD]456
[/TD]
[TD]789
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]bbb
[/TD]
[TD]123
[/TD]
[TD]456
[/TD]
[TD]789
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]ccc
[/TD]
[TD]123
[/TD]
[TD]456
[/TD]
[TD]789
[/TD]
[/TR]
</tbody>[/TABLE]

On any row where "Value" is "B," I want to do the following

- Change Col1 to "MyChangedValue"
- Replace contents in Col2, Col3, and Col4 with null

The output should look like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Value
[/TD]
[TD]Col1
[/TD]
[TD]Col2
[/TD]
[TD]Col3
[/TD]
[TD]Col4
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]aaa
[/TD]
[TD]123
[/TD]
[TD]456
[/TD]
[TD]789
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]MyChangedValue
[/TD]
[TD]null
[/TD]
[TD]null
[/TD]
[TD]null
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]ccc
[/TD]
[TD]123
[/TD]
[TD]456
[/TD]
[TD]789
[/TD]
[/TR]
</tbody>[/TABLE]


The only way I know how to do this currently is to add a conditional column for each one, and perform the test "if [Value] = "B" then..."

Is there a better approach to this than adding 4 new columns and then removing the old ones? In some cases, I may have up to 8 columns requiring modification like this.

Thanks

 

[billszysz]

One of possible ways

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
     Result = Table.ExpandRecordColumn(Table.AddColumn(Source, "Records", each if [Value] = "B" then Record.FromList( {"B", "MyChangedValue"},   List.FirstN(Table.ColumnNames(Source),2)) else _)[[Records]], "Records",Table.ColumnNames(Source))
in
     Result

 

[MarcelBeug]

Two more possible ways. Table1 is already imported into Power Query and typed.

let
    Source = Table1,
    Custom1 = Table.Combine(Table.TransformRows(Source, each if [Value] = "B" then #table(Value.Type(Source),{{"B","MyChangedValue",null,null,null}}) else Table.FromRecords({_},Value.Type(Source))))
in
    Custom1

let
    Source = Table1,
    Result = Value.ReplaceType(Table.Combine(Table.TransformRows(Source, each if [Value] = "B" then #table(List.FirstN(Table.ColumnNames(Source),2),{{"B","MyChangedValue"}}) else Table.FromRecords({_}))),Value.Type(Source))
in
    Result