TransformColumns if null then pull data from another column

[mduntley]

I am trying to find a way so if there is a data in column 3 it will change it to null, but if the data is null, then get the value from column 2. I know I can add another column to complete this task, but I am trying to use the TransformColumns function more

let
    Source = #table({"Column 1", "Column 2", "Column 3"}, {{"439","772","748"},{"3241","1143","740"},{"1552",null,"531"},{"1308","2494","161"},{"3562","1527",null},{"2043","2162","513"},{"1749","1646","26"},{"3472",null,"427"},{null,"456",null}}),
    Custom1 = Table.TransformColumns(Source, {"Column 3", each if _ is null then [Column 2] else null})
in
    Custom1

 

[JGordon11]

Table.TransformColumns isn't going to work for this example. It doesn't provide the row context to reference Column 2 while transforming Column 3. You can accomplish this with Table.TransformRows as below:

let
    Source = #table({"Column 1", "Column 2", "Column 3"}, {{"439","772","748"},{"3241","1143","740"},{"1552",null,"531"},{"1308","2494","161"},{"3562","1527",null},{"2043","2162","513"},{"1749","1646","26"},{"3472",null,"427"},{null,"456",null}}),
    Result = Table.FromRecords(Table.TransformRows(Source, each Record.TransformFields(_, {"Column 3", (x)=> if x=null then [Column 2] else null})))
in
    Result

 

[mduntley]

Table.TransformColumns isn't going to work for this example. It doesn't provide the row context to reference Column 2 while transforming Column 3. You can accomplish this with Table.TransformRows as below:

let
    Source = #table({"Column 1", "Column 2", "Column 3"}, {{"439","772","748"},{"3241","1143","740"},{"1552",null,"531"},{"1308","2494","161"},{"3562","1527",null},{"2043","2162","513"},{"1749","1646","26"},{"3472",null,"427"},{null,"456",null}}),
    Result = Table.FromRecords(Table.TransformRows(Source, each Record.TransformFields(_, {"Column 3", (x)=> if x=null then [Column 2] else null})))
in
    Result

What will if its be if the column 3 contain a specific text?

 

[GraH]

Can be achieved also with a replace value, like below.

let
    Source = #table({"Column 1", "Column 2", "Column 3"}, {{"439","772","748"},{"3241","1143","740"},{"1552",null,"531"},{"1308","2494","161"},{"3562","1527",null},{"2043","2162","513"},{"1749","1646","26"},{"3472",null,"427"},{null,"456",null}}),
    #"Replaced Value" = Table.ReplaceValue(Source,each [Column 3],each if [Column 3] = null then [Column 2] else null,Replacer.ReplaceValue,{"Column 3"})
   
in
    #"Replaced Value"

Adjust in this part here if [Column 3] = null then [Column 2] else null, to your needs