Forum Discussion
Replacing a column with calculation, performing a column lookup dynamically
Hi, I have a vendor file that has numbers that I need to normalize in order to display the correct results. The calculation I need to do is to pull in existing data, overwrite the score data based o...
Add a custom step with the following. Replace #"Changed Type" with the name of your last step (that is the default value when you add a custom step).
= Table.FromRecords(Table.TransformRows(#"Changed Type", each _ & Record.FromList(List.Transform(List.Select(Record.FieldNames(_), each Text.StartsWith(_, "Score_")), (name) => Record.Field(_, name) * Record.Field(_, "Weight_" & Text.AfterDelimiter(name, "Score_"))),List.Select(Record.FieldNames(_), each Text.StartsWith(_, "Score_")))), Value.Type(#"Changed Type"))
Yea, this is an alternative solution that works on the base table. It won't do anything unless there are Score_# and Weight_# columns
