Forum Discussion

Centaur's avatar
Centaur
Helper V
4 years ago
Solved

Move cell to another Column

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 t...
Tips and Tricks
  • SteveHailey's avatar
    SteveHailey
    4 years ago

    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.

SteveHailey's avatar
SteveHailey
Solution Specialist
4 years ago

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's avatar
Centaur
Helper V
4 years ago

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's avatar
    SteveHailey
    Solution Specialist
    4 years ago

    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.