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

     

edhans's avatar
edhans
Community Champion
6 years ago

No. that first line is what I pasted in using the Enter Data feature.

 

Your source would the data connection to your 299 column file. My code should work on all columns - that is be beauty of UnPivot. See the link I posted above on how to integrate my code sample into your actual table. It has images on how to merge the code, and a link at the bottom of that post to a video Imke made for this as well.

You can then remove unneeded columns and rename columns to suit your needs for subsequent merges.

 

Please post back if you have any specific questions on being unable to do  the code merge and I'll assist. I'd need your M code to do it though. Otherwise, if you can mark my post as the solution it would be appreciated, and also let others know this is resolved and there is an answer for future users searching similar scenarios.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks edhans , artemus .  I was trying to add that last step, having issues but this looks like the solution that I am looking for.

 

The last step gave me a "comma token expected" error.

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"