0

In power query, I have a list of tables, each with 6 rows and 2 columns.

I have a separate list of 6 values (1-6). I would like to add that list column (values 1-6) to each table within my list of tables.

What is the most appropriate M code to use?

Picture of what I want to achieve

Thanks

I was trying some Table functions, but unsuccessful

Gerard Duggan
  • 25
  • 4
  • Does this answer your question? [Insert new column with list of values in PowerQuery/M](https://stackoverflow.com/questions/59914300/insert-new-column-with-list-of-values-in-powerquery-m) – Luke_0 May 31 '23 at 14:05

1 Answers1

1

Is this what you want? Appends a column to each table within a list of tables

// sample data set up
let Source = #table({"Column1", "Column2"},{{"A","B"},{"C","D"},{"E","F"},{"G","H"},{"I","J"},{"K","L"}}),
ListofTables = List.Transform({1..5}, each Source),
NewListColumn = #table({"Column1"},{{"M"},{"N"},{"O"},{"P"},{"Q"},{"R"}}),

// Appends a column to each table within a list of tables:
AppendNewListColumn=List.Transform (ListofTables, each Table.FromColumns(Table.ToColumns(_) & Table.ToColumns(NewListColumn),Table.ColumnNames(_)& {"New"}))
in AppendNewListColumn

enter image description here

To put new column first before other columns

 AppendNewListColumn=List.Transform (ListofTables, each Table.FromColumns(Table.ToColumns(NewListColumn)&Table.ToColumns(_),{"New"}&Table.ColumnNames(_)))
horseyride
  • 17,007
  • 2
  • 11
  • 22
  • As an aside, if I wanted the "New" column to be the first column in each table, is there much of a modification to the code? – Gerard Duggan May 31 '23 at 14:48
  • Note if all you are doing is numbering rows then use Table.AddIndexColumn(_, "Index",1,1) as the transform. You dont need the rest of the junk – horseyride May 31 '23 at 14:53
  • Thanks again, I was getting my ( ) mixed. This helped me trim my code down a lot, removing a lot of more manual steps. – Gerard Duggan May 31 '23 at 15:06
  • Yes, I was tempted to use an index, but there is the potential that the numbering may change/differ on the real data set. Thanks again. – Gerard Duggan May 31 '23 at 15:08