Move cell to another Column

[Centaur]

Hello, I am a novice user of PQ.  I have a column named [Invoice Date] and there are a few entries that are not dates and I need to move them to the [Vendor Name] column.  There are many cells with the words as shown below in the [Invoice Date] column.  I would like to find all occurences and move them.  How can I do this? 

thank you. 

[Centaur]

Hi Steve, thank you very much.  Its working great!  Thank you Daniel as well for the advanced code.  I learned something new.  Happy new year. 

[wdx223_Daniel]

NewStep=Table.FromRecords(Table.TransformRows(PreviousStepName,each if [Invoice date] is text then _&[Vendor=[Invoice date],Invoice date=null] else _))

[Centaur]

Hi, Sorry but I didnt see your post.  

Where do I add that formula?  I tried to add a new column and in the formula bar but it says it doesnt recognize NewStep after I click the green check.  I took "NewStep" out then says doesnt recognize PreviousStepName.  Apologies but I not too familar with Power Query.  Grateful for your response. 

Here is where I enter it after clicking Add Column, Custom Column: 

but after I click the green check it returns the below: 

[Centaur]

Hi, not sure if you noticed I responded.  thank you...

[Centaur]

wow Steve that is amazing.  I think I can do that.  Will revert if any issues.  thank you. 

[SteveHailey]

You could go to Add Column > Conditional Column, and create the logic as seen in the screenshot below. Then, Right click > Remove the original vendor column, then rename the new "Vendor New" column to the original "Vendor".

Or, equivalently, you could create a Custom Column with this code:

if [Invoice Date] = "Local Account" then [Invoice Date]
else if [Invoice Date] = "Depository Account" then [Invoice Date]
else if [Invoice Date] = "Plus Liquidity Reserve" then [Invoice Date]
else [Vendor]

-Steve

[Centaur]

Steve, after adding the conditional column and moving the fields that have the text in Invoice Date they have successfully moved from Invoice Date column to the VEndor column however the text in quotes still resides in the Invoice Date column.  How do I move the text in quotes out of the Invoice Date column as well?  Should I make another condtional column? 

[SteveHailey]

To move the text in quotes out, I would use  "Replace Values". So right click the Invoice Date column, and choose Replace Values.

In the Replace Values dialog box, in the "Value To Find" box, enter "Local Account" (but without the quotes!). And leave the "Replace With" box blank.

Then repeat for other two items.

[Centaur]

HI Steve, I think I need to account for misspellings.  Daniel's solution seems to be a workaround for this but not sure how to implement it. 

[SteveHailey · Accepted solution]

Hi Centaur. Danie's solution is definitely more robust. But it requires a little more advanced Power Query knowledge. His code doesn't go in the Custom Column box. It goes in the Advanced Editor, which you can get to by clicking View in the ribbon, then Advanced Editor. 

You will want to add his code near the bottom, just above the line that says "in". And where it says "PreviousStepName", you will want to replace that with the name of the step that previously on the line just above the "in". You'll also need to adjust the name of the step in the line below the "in" to NewStep.

So, for example, if your code in the advanced editor looked like this previously: 

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {"Invoice Date", type any}})
in
    #"Changed Type"

then you would change it to something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {"Invoice date", type any}}),
    NewStep=Table.FromRecords(Table.TransformRows(#"Changed Type",each if [Invoice date] is text then _&[Vendor=[Invoice date],Invoice date=null] else _))
in
    NewStep

Also make sure that new line doesn't have a comma at the end. But the line that was previously just above the "in" does have a comma.