Move contents of field to another column

[Centaur]

Hello,

I have a field "Currency" that is text (3 letters) and I have many currencies in this column but I have also the word "Total" and I want to remove it and place it in the Vendor column so that the word "Total" is moved out of "Currency" and moved into "Vendor".  I added the below as a conditional column but doesnt seem to be what I am after because  Total is not moved out of the Currency column (it still resides there).  I am a novice user of Power Query.  There are a few NULL values in Currency and need to account for them as I was getting the red cross hatch on the new column and after I added the null part the error went away (I can not filter Null out though as there is data in those rows). 

What am I doing wrong? 

Conditional Column: 

= Table.AddColumn(#"Changed Type6", "Custom", each if [Currency] = "Total" then [Currency] else if [Currency] = "" then [Currency] else [Vendor])

thank you very much

[Anonymous]

When you need to create/remove multiple columns in one step, you can use  https://docs.microsoft.com/en-us/powerquery-m/table-transformrows

I'm not totally visualizing OP's table, so here's one that mutates 3 columns into 2

let
    // creates a table for the example
    Source = #table(
        type table[A = text, B = number, C = number], 
        {{"a",2,3}, {"b",4,5}}),

    transformRows = Table.TransformRows(
        Source,
        (row) => [ 
            A = "Prefix" & row[A], 
            Sum = row[B] + row[C]
        ]
    ),
    FinalTable = Table.FromRecords( transformRows,
        type table[A = text, Sum = nullable number] )
in
    FinalTable

[AlexisOlson]

You can't do this with a single calculated column since you also want to modify the [Vendor] column.

Try defining a new currency column and a new vendor column (you can remove the old ones when you're done).

Table.AddColumn(#"Changed Type6", "CurrencyNew", each if [Currency] = "Total" then "" else [Currency])

Table.AddColumn(#"Add Custom9", "VendorNew", each if [Currency] = "Total" then "Total" else [Vendor])

[Centaur]

Hi Alexis,

do I add that from the Add Column, Custom Column?  I tried but it only returned the word "table" so I think I did somethign wrong.  Sorry but I am a novice user.  thank you. 

here is a shot of the Advanced Editor (not edited).

[AlexisOlson · Accepted solution]

Using add custom column from the GUI, you just need to put in the rules in the pop-up window.

if [Currency] = "Total" then "" else [Currency]

if [Currency] = "Total" then "Total" else [Vendor]

[Centaur]

Hi Alexis, 

Could I add an "or" statement to the following:

if [Currency] = "Total" then "" else [Currency]

 to something like this:

if [Currency] = "Total" or "Balance" then "" else [Currency]

I did get an error when adding the Or.  

thank you very much. 

[AlexisOlson]

"Balance" is not something you can "or" with.

Try this:

if [Currency] = "Total" or [Currency] = "Balance" then "" else [Currency]

[Centaur]

Nice.  thank you. that worked perfectly.