Forum Discussion
Using M language, how to duplicate a column from one table in another table
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.
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.
3 Replies
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.
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.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.
