Search code examples
excelvbapowerquerym

Adding Columns using Power Queries in excel that copy the first row of the next column

I'm looking to create new columns to the left of columns 3 through 42 that simply copy the first value from the column to their right. In the picture, then, I'm looking for a column next to Column3 which just has "Auto" for every entry and then one to the left of Column4 that is full of "Home" and so on and so forth. Current State

I got help to create queries that copy the value from Column3, "Auto", and then create a new column at the end of my data with that value, but I'm sure there's a way to make a loop that could simultaneously do this for every one of my columns (3-42) and maybe even put the columns where they're supposed to go (to the left of their matching column). Here is a photo of the queries I have thusfar: current queries

Any help is greatly appreciated. I'm just learning how to use Power Query! Thanks so much.

Solution

  • This seems to work okay if you remove your existing

    in
#"Added Custom"

    and append this to your code in home ... advanced editor

    First2Cols = List.FirstN(Table.ColumnNames(#"Added Custom"),2),
AdditionalCols = List.Range(Table.ColumnNames(#"Added Custom"),2),
TopRow = Table.RemoveColumns(Table.FirstN(#"Added Custom",1),First2Cols),
NewNames=List.Transform(AdditionalCols , each  _ & "_"),
CombineTables = TopRow & Table.RenameColumns( #"Added Custom" , List.Zip( { AdditionalCols, NewNames} ) ),
#"Filled Down" = Table.Skip(Table.FillDown(CombineTables,AdditionalCols),1),
#"Reordered Columns" = Table.ReorderColumns(#"Filled Down", First2Cols & List.Sort( NewNames & AdditionalCols) )
in  #"Reordered Columns"

    you should then probably promote the first row to headers