cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TbombToronto
Frequent Visitor

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 on a normalized calculation.  The calculation is 100 - score/weight.  Everything comes back as a column.  Here is the sample fille:

 

NameScore_1Weight_1Score_2Weight_2Score_3Weight_3
James2.20.11.50.21.30.3
Mark1.20.22.70.41.80.2
Cooper4.40.16.20.62.20.3
Ellie1.90.37.30.42.60.4
Chase6.30.053.60.22.20.5
Amanda8.50.15.60.32.30.6
Anastasia6.30.13.40.22.30.5

 

What I need to do in this sample is for score_1, replace score_1 with a calculation for 100 - score_1/weight_1, and the same for score_2, it would be 100 - score_2/weight_2... and on and on.  There are 300 columns, so score_299 = 100 - score_299/weight_299.

 

I definitely don't want to do a replaced value on each of the columns, as I would need to do 299 of them.  Is there a formula or more systemic way to do this?

 

Thanks,

Trieu

1 ACCEPTED SOLUTION
artemus
Microsoft
Microsoft

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

 

View solution in original post

12 REPLIES 12
artemus
Microsoft
Microsoft

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

 

View solution in original post

edhans
Super User III
Super User III

Try this @TbombToronto 

It turns this table:

edhans_0-1597849403678.png

into this:

edhans_1-1597849432414.png

What it does is:

  1. Normalizes the data by unpivoting all of the score/weight columns
  2. Splits the score_1 into two fields (score, 1)
  3. Pivots the category that has the score/weight lables so they are now in columns
  4. adds a custom column that does the math. If weight is 0 it returns 0 - otherwise you'd get an infinity answer which you likely do not want.
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", Int64.Type}, {"Weight_1", Int64.Type}, {"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
)
in
    #"Added Custom"

From there you can transform as desired to return only what you need to the DAX model for visuals.

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks Edhans, so in this section, if I have 299 columns, I would need to put in all of them?  In the end I will need to unpivot anyway as that is how I need to report it so that works out.

 

 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]),

Also, can I just replace the score column instead of creating a new one?  I have to actually take this and do a merge query to do a compare so the column name needs to stay the same. 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

@artemus  That works!  Thank you.  This is exactly what I was looking for.

 

@edhans thank you as well, I just needed it to happen on the same column but your solution will help me on another issue.

Great @TbombToronto - glad you have the solution you needed!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@TbombToronto - you should either use my solution, or use @artemus solution. I was unable to get the desired results (as I understood them) with what @artemus provided and I am looking at that code as it is interesting, but I do not believe that code was intended to be added to the code I did but to your source table. @artemus can correct me if I am wrong.

LIke Excel, Power BI often has many solutions to the same problem. 👍



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

It looks like you have an extra ) at the end

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"

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors