CODEWITHSUNDEEP
excelpowerquerycustom-code
Radosław Poprawski
Radosław Poprawski

Reputation: 3

Power Query - duplicate multiple columns Mcode

I need to duplicate multiple columns with PQ, and yes I can do it manually having either 10 steps or 1 entangled step with 10 duplicate column commands.

while I had similar problem with adding columns I used:

#"Added Custom" =
  Table.AddColumn(#"Changed Type", "record", each [
      Custom = "DTO",
      #"Custom.1" = Date.ToText([Accounting Date],"YYYYMMDD"),
      #"Custom.2" = "SA",
      #"Custom.3" = "DTO " & Date.ToText([Accounting Date], "yyyy.MM"),
      #"Custom.4" = null,
      #"Custom.5" = null,
      #"Custom.6" = null,
      #"Custom.7" = null,
      #"Custom.8" = null,
      #"Custom.9" = if Text.StartsWith([General Ledger Code],"5") then [Cost Center] else null,
      #"Custom.10" = null,
      #"Custom.11" = null,
      #"Custom.12" = null,
      #"Custom.13" = if Text.StartsWith([General Ledger Code], "5") then "IF" else null
    ]),
#"Expanded record" = Table.ExpandRecordColumn(#"Added Custom", "record", {"Custom", "Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13"}, {"Custom", "Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13"}),

and it worked for adding columns

however I failed while trying to duplicate this with:

#"Duplicated test" = Table.AddColumn(#"Expanded record", "duplicated", each [
      #"Custom.14" = Table.DuplicateColumn(#"Expanded record", "Custom.1", "Custom.1 - Copy"),
      #"Custom.15" = Table.DuplicateColumn(#"Expanded record", "Custom.3", "Custom.3 - Copy")     
]),
#"Expanded duplicated" = Table.ExpandRecordColumn(#"Duplicated test", "duplicated", {"Custom.14", "Custom.15"}, {"Custom.14", "Custom.15"}),

After expanding columns i received my whole table instead of just 2 additional columns.

Is there a way to simplify duplicating columns like adding columns?

Upvotes: 0

Views: 4278

Answers (2)

Kade
Kade

Reputation: 1022

Alternatively, you can use List.Accumulate to make it like you are passing a simple list of column names to the native Table.DuplicateColumn function like this:

table_source = Table.FromRecords({
    [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
    [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
    [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
    [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
}),
table_duplicated = List.Accumulate({"Name", "Phone"}, table_source, (state as table, current as text) => 
    Table.DuplicateColumn(state, current, current & " - Copy")
)

Here's what the output looks like: Power Query Screenshot with tow duplicated columns

Upvotes: 0

Patrick FitzGerald
Patrick FitzGerald

Reputation: 3630

Yes, you can simplify duplicating the columns by just using the Table.AddColumn function as you did in the first step, no need for the Table.DuplicateColumn function:

#"Added Custom1" = Table.AddColumn(#"Expanded record", "duplicated", each [
        Custom.14 = [Custom.1],
        Custom.15 = [Custom.3]
        ]),
#"Expanded duplicated" = Table.ExpandRecordColumn(#"Added Custom1", "duplicated", {"Custom.14", "Custom.15"}, {"Custom.14", "Custom.15"})

Upvotes: 1

Related Questions