Using M language, how to duplicate a column from one table in another table

[bmcminn]

I know this should be straightforward, but how do I duplicate a column from one table in another table? In the orginal table it is a whole number that comes from subtracting one date from another and then dividing by seven to get number of weeks. I tried

#"Added Custom" = Table.AddColumn(#"Added Week Date", "Week to Semester", each #"5_19_23 Current Raw Data"[Week to Semester]),

but that yields a list of the original column in each cell.

I tried copy and paste too, that didn't seem to work. Thanks. Maybe @MarcelBeug has an idea. I learned how to make a reference data parameter in another question he answered.

[bmcminn]

So I should rephrase this. I have a column in TableA with a repeating value, 20, that is calculated using some other columns. I want to copy that value and put it into a column in TableB. Becuase of the requirements of the project, this need to be done using M language so that the steps can be replicated automatically as the data refreshes and the value in TableA changes.

This morning I tried

#"Added Custom" = Table.AddColumn(#"Added Application Semester", "Calendar Week Copy", each "5/19/23 Current Raw Data"[Calendar Week]{1})

and I can imagine that the error is the same as @danextian mentions above, but I don't understand how to use the filter logic.

Thank you.

[bmcminn · Accepted solution]

I ended up using a very simple solution because it was all just a syntax problem. What worked to copy a value from one table and from a particular cell into every other cell of a new column in a different table:

let
  Source = stuff,
  #"Other stuff",
  #"Added Columnn w/ Copied Value" = Table.AddColumn(#"Other stuff", "Columnn w/ Copied Value", each #"That Other Table"[Target Column]{0})
in
  #"Added Week of Year 2023"

with {0} being the index value.

[danextian]

Hi @bmcminn ,

The below script creates a list based on [Week to Semester] column from "5_19_23 Current Raw Data" table.

#"5_19_23 Current Raw Data"[Week to Semester]

You did not specify how to identify which row/s in the created list each row in the current table should pick thus you end up with the same information for  each row. You can either do a merge or use the same list but specify a filter logic similar to below

let 
lookupvalue = [column in the current table],
lookupcolumn = othertable[lookup column], 
resultcolumn = othertable[result column]
in resultcolumn{List.PositionOf(lookupcolumn,lookupvalue]}

The above code as a custom column determines the row position of the lookupvalue and picks up the value of that position from the  resultcolumn list. Position of the first row is zero.