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,

Value	Col1	Col2	Col3	Col4
A	aaa	123	456	789
B	bbb	123	456	789
C	ccc	123	456	789

<tbody>
</tbody>

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:

Value	Col1	Col2	Col3	Col4
A	aaa	123	456	789
B	MyChangedValue	null	null	null
C	ccc	123	456	789

<tbody>
</tbody>

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