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.

Ron Rosenfeld · Accepted Answer · 2022-01-24 12:09:36Z

2

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

edited Jan 24, 2022 at 12:09

answered Jan 23, 2022 at 21:04

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

UBP Over a year ago

This worked perfectly Ron thanks. One follow up I had is do you know of a way I could add a conditional on the FillUp part of the code? So if Table1.Col1 = False then the dynamic column created = 0 , if True then dynamic column = Table2.Values

Ron Rosenfeld Over a year ago

@UBP Not sure exactly what you mean. But if you want to have something happen to the subsequent columns in the row that has false in the first column from table 1, I'd suggest doing a Table.TransformRows operation after you've created the above table. Might be best to pose this as a new question, where you can post the table data again along with desired outcome for this new set of conditions, along with your effort at solving it.

UBP Over a year ago

just asked a follow up here stackoverflow.com/questions/70870939/…

Ron Rosenfeld Over a year ago

@UBP I won't have time to look at your follow-up for a few days, but you can find some help doing a search for "transforming a column based on another column".

horseyride · Accepted Answer · 2022-01-24 21:56:01Z

0

My version

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
custom = Table.FromColumns(Table.ToColumns(Source) &Table.ToColumns(Table2), List.Combine({Table.ColumnNames(Source),Table.ColumnNames(Table2)})  ),
#"Filled Down" = Table.FillDown(custom,Table.ColumnNames(Table2))
in  #"Filled Down"

edited Jan 24, 2022 at 21:56

answered Jan 24, 2022 at 20:47

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Collectives™ on Stack Overflow

Add Custom and Dynamic columns

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related