Forum Discussion
How to add list to table in single step?
Hi all,
I have this code
= Table.AddColumn(
Table.FromRecords({[A = 1],[A = 2],[A = 3],[A = 4]}), //Column "A"
"NewData", // New column name
each
let
z = { "b",4,7,"av" } // List to add as new column
in z,
type number
)
for which I'm getting this output
The output I'm looking for with a single step is like this.
It seems I'm near of expected output but instead of each value in list appears "List" in each row. How can I do this without helper columns?
I wish I knew a better way but here's one possible method:
let Source = Table.FromRecords({[A = 1],[A = 2],[A = 3],[A = 4]}), z = {"b",4,7,"av"}, AddCol = Table.FromColumns(Table.ToColumns(Source) & {z}, Table.ColumnNames(Source) & {"NewData"}) in AddCol
3 Replies
I wish I knew a better way but here's one possible method:
let Source = Table.FromRecords({[A = 1],[A = 2],[A = 3],[A = 4]}), z = {"b",4,7,"av"}, AddCol = Table.FromColumns(Table.ToColumns(Source) & {z}, Table.ColumnNames(Source) & {"NewData"}) in AddColHere's 3 ways to do it:
1. The simplest way would just to use the Enter Data button and enter those values manually.
2. This article describes one way to do it with M code.
= #table(type table [A = Text.Type, NewData = Text.Type], {{1,"b"}, {2,4},{3,7},{4,"av"}})
3. Use Table.FromColumns
let Source = Table.FromRecords({[A = 1],[A = 2],[A = 3],[A = 4]}), Output = Table.FromColumns(Table.ToColumns(Source) & {{"b",4,7,"av"}}) in OutputPat
ppm1 AlexisOlson Thanks both for your help, your solutions are almost the same and works pretty fine. I was trying to know if within the command "Table.AddColumn(...)" is possible to do the same, since this command allows features like use "each" inside of it. If not, I'll use your solutions.
One more question.
" If the list contains only numbers, how to tell the type is Int64 during the concatenation of Source and the List?
Something like
Output = Table.FromColumns(Table.ToColumns(Source) & ({{1..4}},type number))
