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,
<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:
<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
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
Last edited: