Add Custom and Dynamic columns

Question

I have two tables and am trying to figure out how to create custom code to add dynamic columns with custom names that are based off of row values in another table. I then need to use the values of rows in Table 2 to not only create the column names but also fill the new dynamic Columns with a value from another column in Table 2. Hopefully my pictures below help

Table 1 has varying amount of rows depending on what the user input. Table 2 has varying amount of rows depending on how many values the user inputs.

Table 1 Before

Col1	Col2	Col 3
stuff 1	stuff 2	stuff 3
stuff 4	stuff 5	stuff 6
.	.	.
.	.	.

Table 2

Name	Values
Name1	100
Name2	500
.	.
NameX	Y

Table 1 After

Col1	Col2	Col 3	"Column" & Name1	"Column"& Name2	...	"Column"& NameX
stuff 1	stuff 2	stuff 3	100	500	...	Y
stuff 4	stuff 5	stuff 6	100	500	...	Y
.	.	.	100	500	...	Y
.	.	.	100	500	...	Y

The "Column" & Name1 meaning I want to concatenate Column with the values in the Name column in Table 2.

Answer 1

You can make this dynamic by not referring to the absolute column names, but rather using the Table.ColumnNames function to return those names.

I did assume that the column names in Table 2 are fixed. If not, that code can be changed.

Read the code comments and examine the Applied Steps window to better understand the methods used. There are examples of setting the data type, and also re-naming columns without referring to a hard-coded column name.

M Code

let
//read in the  two tables and set the data types
    Source1 = Excel.CurrentWorkbook(){[Name="Table_2"]}[Content],
    Table2 =Table.TransformColumnTypes(Source1,
        {{"Name", type text},{"Values", type any}}),

    Source = Excel.CurrentWorkbook(){[Name="Table_1_Before"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, type text})),

//create the extra columns by
    //Transpose Table2
    //  Use first row as headers
    xpose = Table.Transpose(Table2),
    #"Promoted Headers" = Table.PromoteHeaders(xpose, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",
        List.Transform(Table.ColumnNames(#"Promoted Headers"), each {_, type any})),

    //rename the columns
    renameNameCols = Table.RenameColumns(#"Changed Type1", 
        List.Zip(
            {Table.ColumnNames(#"Changed Type1"), 
            List.Transform(Table.ColumnNames(#"Changed Type1"), each "Column " & _)})),

//Combine the tables
    combine = Table.Combine({#"Changed Type",renameNameCols}),

//fill up the original table 2 columns and remove the blank Table 1 rows
    #"Filled Up" = Table.FillUp(combine,Table.ColumnNames(renameNameCols)),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Col1] <> null))
    
in
    #"Filtered Rows"

Original Tables

Results

Note that I did NOT add logic to avoid prepending the ... with the word column, as shown in your desired output, but that is easily added if really needed