Forum Discussion

Copper Contributor

Mar 17, 2023

Solved

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...

excel

Formulas and Functions

SergeiBaklan

Mar 18, 2023

Mark_Desmarais

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

SergeiBaklan

Diamond Contributor

Mar 18, 2023

Mark_Desmarais

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

Mark_Desmarais

Copper Contributor

Mar 18, 2023

SergeiBaklan 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.

SergeiBaklan

Diamond Contributor

Mar 19, 2023

Mark_Desmarais

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
    tableWithDuplicatedColumns

based on previous sample