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"))
Thanks edhans and artemus . I was trying to add the last step and got an error "Comma token expected".
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY/NCsIwEITfJecQmt/Wo4gXwScIPSwYsNgfaXx/TKar5LIMs19mNjGKGy0pCymMMmV2SpeplYc20BbailFGcaf9BdP8AaN6aAd/YL/Cl217p70YDssjO/DT0HQe2dd5nhJCTuxK0XO7Ax1YI/xJOSHvILp6smXENOEe+Hmh9UHFGPhv9RbPuAVu+S7gK+UP5YmaBo0C1xTYX8H4BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Score_1 = _t, Weight_1 = _t, Score_2 = _t, Weight_2 = _t, Score_3 = _t, Weight_3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Score_1", type number}, {"Weight_1", type number}, {"Score_2", Int64.Type}, {"Weight_2", Int64.Type}, {"Score_3", Int64.Type}, {"Weight_3", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Category", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Category", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Category", "Version"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Category]), "Category", "Value"),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "New Value", each if [Weight] = 0 then 0 else 100 - [Score] / [Weight], Int64.Type
),
#"Added Custom1" = Table.FromRecords(Table.TransformRows(#"Added Custom", 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(#"Added Custom")))
in
#"Added Custom1"Oops, had an extra ")" in there somehow. When it ran it did not replace the original column.
Yea, this is an alternative solution that works on the base table. It won't do anything unless there are Score_# and Weight_# columns
