How to shift column "text" to the neighboring one

[grzego12]

Hi All,

I am trying to shift a text from one column to a neighbouring one.

While importing data from CSV I have ended up with row containing my "names" that are shifted versus where I needed them to be.

I there an easy way to move "text" (CH1:Open Circuit) from column 1 to column 2 (blank)

for all columns?

1        !   2  !    3      !      4   !    5      !      6  !

name !       !  name !           ! name  !          !

to

1  !     2    !   3    !    4     !   5    !    6       !

    ! name !        !  name !         !   name !

I have tried some sort of merging by extracting only relevant names, removing blanks and transposing thus creating a table with names... unfortunately merging creates a mess with the data not aligning at all.

[AllisonKennedy]

@grzego12 

How many columns are there which are misaligned? 

My first advice would be to check the Source settings on your CSV file and ensure there isn't something you can fix in there to get the columns to align properly. 

If that doesn't work, you might be able to get your desired result using a transpose and Fill Down. Then transpose again. 

Another Option: 

You could either use the Row Index for this, or use a conditional column and basically say If column2 is blank, then column1 else column 2. There is probably a way to write a custom function that will repeat this for every column pair you need, but I'm not sure. @ImkeF do you have any ideas? 

This would be how to add the correct Column 2 values: 

= Table.AddColumn(#"Removed BlankRows1", "Column2 Fixed", each if [Column2] = null then [Column1] else [Column2])

You would need to then delete the original column 2 and repeat for every iteration, so not very efficient. 

Did you also want to remove the text from Column1 as well once it is copied into Column2? 

[ImkeF · Accepted solution]

Hi @grzego12 ,

instead of shifting the data, I would "shift" the column names like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTVS0lECIhDTBME0AzFjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    OldColumnNames = Table.ColumnNames(Source),
    NewColumnNames = List.Skip(OldColumnNames) & {"LastColumn"},
    NestedRenameList = List.Zip({OldColumnNames, NewColumnNames}),
    #"Renamed Columns" = Table.RenameColumns(Source, NestedRenameList)
in
    #"Renamed Columns"

See also attached file.

[grzego12]

Hi All,

Thank you for your help. Much appreciated. Finally I can get cracking with my data !

[AllisonKennedy]

@ImkeF Is there a skip last value so we can shift the column names to the right instead of left?

So basically for the OP, they would promote headers, shift column headers right, then demote headers?

I'm not sure what the end goal actually is, because if you demote headers then you will lose the null values for repeated columns....

[ImkeF]

Hi @AllisonKennedy ,

yes, it should work like that.
Sure, you can shift into the other direction instead. But I thought my original solution looked good, TBH 🙂
Anyhow, it would work like so:

  NewColumnNames = {"FirstColumn"} &  List.RemoveLastN(OldColumnNames,1),

See file attached.

[AllisonKennedy]

Pleasure learning from you, thanks @ImkeH

[pranit828]

Hi @grzego12 

Right click on the name of the last loclumn and select Move --> To Begining

You can do it on all other columns as Left, Right or To End

[grzego12]

Hi Pranit828,

Much appreciate your response but I did not want to move whole column.

I would like to shift a single row or "cell with text" to the "right" into the neighbouring "cell".

Eventually automatically copy "name" from a cell into a neighbouring cell within a single row.

in excell you could just do it with "=" sign but here its just get complicated.