Forum Discussion
New to Power Query - Duplicate All Columns
Hello, I have a fairly simple problem I'm not sure how to approach.
I have a table in Power Query and I want to duplicate all the columns (and modify their names) without having to do it manually as new columns are added.
For example, I have:
| Column1 | Column2 | Column3 |
And I want it to become
| Column1A | Column1B | Column2A | Column2B | Column3A | Column3B |
I assume the answer here is something like, "get a list of all the column names, run duplicate column on all of them, then rename them" but I'm not sure how to approach that.
You may generate script like this
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], names = Table.ColumnNames (Source), namesA = List.Transform( names, (q) => q & "A"), ourColumns = Table.ToColumns(Source), tableWithDuplicatedColumns = Table.FromColumns( ourColumns & ourColumns, names & namesA) in tableWithDuplicatedColumns
3 Replies
You may generate script like this
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], names = Table.ColumnNames (Source), namesA = List.Transform( names, (q) => q & "A"), ourColumns = Table.ToColumns(Source), tableWithDuplicatedColumns = Table.FromColumns( ourColumns & ourColumns, names & namesA) in tableWithDuplicatedColumnsSergeiBaklan Thank you very much, this is what I was looking for. I appreciate your understanding of the problem.
The only issue I have at the moment is that the columns in this solution are actually organized like this:
Column1A Column2A Column3A Column1B Column2B Column3B Where I'd prefer them like this:
Column1A Column1B Column2A Column2B Column3A Column3B Is there an elegant way to write the step with Table.FromColumns where the columns where the two sets of columns are merged in alternating columns?
Thank you.
That could be like
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], names = Table.ColumnNames (Source), namesA = List.Transform( names, (q) => q & "A"), ourColumns = Table.ToColumns(Source), tableWithDuplicatedColumns = Table.FromColumns( List.Union( List.Zip( {ourColumns, ourColumns} ) ), List.Union( List.Zip( {names, namesA} ) ) ) in tableWithDuplicatedColumnsbased on previous sample
