Power Query: How do I add a specific List/Vector as a Column

IamTheB 0
Viewed 42

I have a simple problem. Let's assume I have the following

enter image description here

Now I want to add a specific vector or list for example (1,2,2,1) as an additional column and it should look like

enter image description here

When I add "Custom Column", how do I have to enter this list that it creates exactly this column? It should not be a index column or a calculated column because I want to specify the values depending on another column manually. It seems like it is a very simple task, but I couldn't find any solutions yet! (I feel very stupid xD)

pls help

Thank you!

powerquery powerbi
2 Answers
let Source = #table({"Column1"},{{"A"},{"B"},{"C"},{"D"}}),
Add = {{1,2,2,1}},
part1=Table.ToColumns(Source) & Add,
part2= Table.FromColumns(part1,Table.ColumnNames(Source)&{"Column2"})
in part2

or perhaps, if grabbing column from another table

let Source = #table({"Column1"},{{"A"},{"B"},{"C"},{"D"}}),
part1=Table.ToColumns(Source) & Table.ToColumns(Table.SelectColumns(SomeOtherTable,"Column9")),
part2 = Table.FromColumns(part1,Table.ColumnNames(Source)&{"Column2"})
in part2
horseyride0
  1. Copy your Column1
  2. Open the Enter Data / Create Table UI and paste Column1
  3. Add your specific vector as Column2 and save the new table
  4. Merge the new table into your original table using both Column1

Note that Column1 has to be a key column (unique values). If it's not, create a temporary index column and use that for merging.

userhiPt0
Related