Insert new column with list of values in PowerQuery/M

Question

If I have the following source:

#"My Source" = Table.FromRecords({
        [Name="Jared Smith", Age=24],
        [Name = "Tom Brady", Age=44],
        [Name="Hello Tom", Age = null],
        [Name = "asdf", Age = "abc"]
    }),

How would I add a new column from a list of values, for example:

Table.AddColumn(#"My Source", "New Col", {'x', 'y', 'z', null})

Now my table would have three columns. How could this be done?

Hi. Did you find a way to append a column without using Index? It seems an overkill to have to create an Index just to append a new column. Thank you. — Confounded, Apr 18 '23 at 01:17

score 5 · Accepted Answer · answered Jan 26 '20 at 16:07

Here's another way. It starts similarly to the approach used by Ron, by adding an index, but then instead of using merge it just uses the index as a reference to the appropriate list item.

let
    Source1 = Table.FromRecords({
        [Name="Jared Smith", Age=24],
        [Name = "Tom Brady", Age=44],
        [Name="Hello Tom", Age = null],
        [Name = "asdf", Age = "abc"]
    }),
    #"Added Index" = Table.AddIndexColumn(Source1, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each {"x", "y", "z", null}{[Index]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

score 2 · Answer 2 · answered Jan 26 '20 at 03:46

I'm a PQ beginner, so there may be more efficient methods, but here's one:

Add an Index column to each of the tables
Merge the two tables, using the Index column as the key
Delete the Index column

let
    Source1 = Table.FromRecords({
        [Name="Jared Smith", Age=24],
        [Name = "Tom Brady", Age=44],
        [Name="Hello Tom", Age = null],
        [Name = "asdf", Age = "abc"]
    }),
    #"Added Index" = Table.AddIndexColumn(Source1, "Index", 0, 1),
    Source2 = Table.FromRecords({
        [New="x"],
        [New = "y"],
        [New = "z"],
        [New = null]
    }),
    #"Added Index2" = Table.AddIndexColumn(Source2, "Index", 0, 1),
    Merge = Table.Join(#"Added Index", "Index",#"Added Index2", "Index"),
    #"Removed Columns" = Table.RemoveColumns(Merge,{"Index"})
in
    #"Removed Columns"

@David542 Yes. If you have large tables, you should try both the Merge method and the method in your accepted answer to see which is faster. — Ron Rosenfeld, Jan 27 '20 at 01:15
thanks, the merge should be faster (I'm guessing, though would have to test to see). This is basically for trivially adding additional data in a quick way. — David542, Jan 27 '20 at 01:16

score 1 · Answer 3 · edited May 22 '23 at 12:25

Here is another solution

let
    #"My Source" = Table.FromRecords({
        [Name="Jared Smith", Age=24],
        [Name = "Tom Brady", Age=44],
        [Name="Hello Tom", Age = null],
        [Name = "asdf", Age = "abc"]
    }),
    AddListAsColumn = Table.FromColumns(Table.ToColumns(#"My Source") & {{"x", "y", "z", null}}, Table.ColumnNames(#"My Source") & {"New Col"})
in AddListAsColumn

Depending on your needs you might need to buffer #"My Source"

Insert new column with list of values in PowerQuery/M

3 Answers3

Linked