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 €"

Answer 2

Easiest way is to create two new columns and then delete the original two. e.g.

Add column - custom column:

Answer 3

your power query code should be like the below

let
    Source = [YourSourceHere], // Replace this with your actual source step
    AddedCustom2 = Table.AddColumn(Source, "New Column2", each if Text.Contains([Column1], "€") then null else [Column1]),
    AddedCustom1 = Table.AddColumn(AddedCustom2, "New Column1", each if Text.Contains([Column1], "€") then [Column1] else null),
    RemovedOriginals = Table.RemoveColumns(AddedCustom1, {"Column1", "Column2"}),
    RenamedColumns = Table.RenameColumns(RemovedOriginals, {{"New Column1", "Column1"}, {"New Column2", "Column2"}})
in
    RenamedColumns

Answer 4

Assuming the source cell is text, pre-append a | then split on that character

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddSplit = Table.TransformColumns(Source,{{"Column1", each if Text.Contains(_,"€") then "|"&_ else _, type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(AddSplit, "Column1", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"})
in  #"Split Column by Delimiter"