Power Query (M Language): How add a column and pupulate with list of values?

[iplaygod]

Hi!

This is a question about Power Query, M Language (not DAX).

I have a table with x number of columns containing data.

Now I want to add a new column, and populate that column with values from a list of values that I have.

Is that possible? How? Please show me an example with code.

Lets say I have a list like

{"banana", "orange", "tomato"}

HOW do I add a column to table and populate that column with those values?
This image explains what I want to do:

Thanks in advance!

[rafaelmpsantos · Accepted solution]

Use a index column (0,1,2,3,4) as a "join" column. 

[rafaelmpsantos]

Something like this

let
Source1 = Table.FromRecords(
{
[Column1 = "A", Column2 = 1],
[Column1 = "B", Column2 = 2],
[Column1 = "C", Column2 = 3]
}
),
Source2 = Table.FromRecords(
{
[Column1 = "A", Column3 = "banana"],
[Column1 = "B", Column3 = "orange"],
[Column1 = "C", Column3 = "tomato"]
}
),
Merge = Table.Join(Source1, "Column1", Source2, "Column1", JoinSide.Left)
in
Merge

[iplaygod]

Thank you both for answering, and you gave me some new ideas, however...

the example I gave was a very simplified generic example.

My real scenario table is 10,000 rows long, and there are no common index keys to join tables together.

And the list I have is actually a list of values that I get from extracting all the values from a column.

So I cannot hardcode the list. I need to use the list that i have extracted/created from another column of values.

Any more ideas?

I really appreciate the answers. thanks

[rafaelmpsantos · Accepted solution]

Use a index column (0,1,2,3,4) as a "join" column. 

[iplaygod]

ok so the steps would be...

1. add index column to existing table

2. create a second temporary table from my list of values

3. add an index column to that second temp table also

4. perform join of the two tables?

Im guessing this could work but its just so cumbersome / many steps. Is there not a more short and elegant way of doing it?
thanks again

[Stachu]

I agree with @rafaelmpsantos, in M this is the most efficient and elegant solution - you can remove the additional columns after the join, so it won't impact your data

do you have an example how would you do it in some other BI software? it may be easier to translate an example to PowerBI logic

[iplaygod]

Ok so for reference for others here is the example code that I came up with

it uses the "join" strategy suggested to add the list as a column to the existing table

Im not crazy about it, but it does get the job done

let
    //retreive table from excel sheet
    source = Excel.CurrentWorkbook(){[Name="Tabell1"]}[Content],
    //add index column to that table
    sourceWithIndex = Table.AddIndexColumn(source, "joinIndex", 0, 1),
    
    //create temp table from a list of values
    //define list or get a list from some place...
    myList = {"banana", "orange", "tomato", "one more"},
    //turn that list into a table
    tempTable = Table.FromColumns({myList}, {"newColumn"}),
    //add index to that table:
    tempTableWithIndex = Table.AddIndexColumn(tempTable, "joinIndex", 0, 1),
    
    //join the two tables tables:
    joinedTable = Table.Join(sourceWithIndex, "joinIndex", tempTableWithIndex, "joinIndex", JoinSide.Left)
    
in
    joinedTable

 
please note: the 4th value in myList does NOT get added to the source table.
Because the source table only has 3 rows, and the list has 4. Therefore the index value for the 4th row finds no match in the source table and therefore is not joined to any row.

[iplaygod · Accepted solution]

and here is a custom M function to do the same
it helps to have a function when doing it repeated times

  //---------------
    //custom function to add a column to table and populate with list of values
    addColumnFromList = ( targetTable, nameForColumn, listForColumnValues) =>
                let 
                   tempTable = Table.FromColumns({listForColumnValues}, {nameForColumn}),
                    //add index to that table:
                    tempTableWithIndex = Table.AddIndexColumn(tempTable, "joinIndex", 0, 1),
                    //add index to passed in table:
                    targetTableWithIndex = Table.AddIndexColumn(targetTable, "joinIndex", 0, 1),
                    joinedTable = Table.Join(targetTableWithIndex , "joinIndex", tempTableWithIndex , "joinIndex", JoinSide.Left),
                    //remove the index column from joined table we dont need it
                    joinedTableWithoutIndex = Table.RemoveColumns(joinedTable, "joinIndex")
                in 
                    joinedTableWithoutIndex,
    //end custom function
    //---------------

[rafaelmpsantos]

YEs these are the steps.

I don't think in anything more easyer to do, using power query of course.

[Stachu]

do you have the values with the correspoding keys?

 e.g. 
A-banana

B-orange

etc.?
then you can achieve so with Merge - you create a join with your values list on a proper key (containing A) in the example
if you don't have the key, what's the logic of assigning values from one table to the other? just order?