Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Name | Score_1 | Weight_1 | Score_2 | Weight_2 | Score_3 | Weight_3 |
James | 2.2 | 0.1 | 1.5 | 0.2 | 1.3 | 0.3 |
Mark | 1.2 | 0.2 | 2.7 | 0.4 | 1.8 | 0.2 |
Cooper | 4.4 | 0.1 | 6.2 | 0.6 | 2.2 | 0.3 |
Ellie | 1.9 | 0.3 | 7.3 | 0.4 | 2.6 | 0.4 |
Chase | 6.3 | 0.05 | 3.6 | 0.2 | 2.2 | 0.5 |
Amanda | 8.5 | 0.1 | 5.6 | 0.3 | 2.3 | 0.6 |
Anastasia | 6.3 | 0.1 | 3.4 | 0.2 | 2.3 | 0.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
Solved! Go to Solution.
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"))
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"))
Try this @TbombToronto
It turns this table:
into this:
What it does is:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks @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
Great @TbombToronto - glad you have the solution you needed!
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. 👍
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIt 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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |