Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
  • artemus's avatar
    artemus
    6 years ago

    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"))

     

artemus's avatar
artemus
Microsoft Employee
6 years ago

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"))