How do I offset the value of a cell to another column?

Question

My goal is to drag over the value of a cell to the corresponding row in another column under a condition.

I have the following table:

Column1	Column2
35	null
27€	null
13	null

If the cell in Column1 contains a "€", then that cell should be emptied and the value brought to Column2.

The output should then be:

Column1	Column2
35	null
null	27€
13	null

The condition is pretty straight-forward, but I'm unfamiliar with the logic of the query editor and how to express the output.

(I'm aware that this can be easily done on Excel or with VBA, but I would like to know how to do these types of tasks directly with Query).

Answer 1

Here's another method with no added columns, using the Table.TransformRows function:

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    
    #"Move €" = Table.FromRecords(
                    Table.TransformRows(#"Changed Type", (r)=>
                        Record.TransformFields(r,  {
                            {"Column1", each if Text.EndsWith(_,"€") then null else _},
                            {"Column2", each if Text.EndsWith(r[Column1],"€") then r[Column1] else _}
                                                    }
                            )
                        )
                    )
in
    #"Move €"