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.
I have a table of Columns containing Attribute counts. I have added a column that does row-wise summation of the Attribute column (denoted by Numbers) values. I want to Divide the Attribute columns by the "Count" column, in-place (replace values) i.e. do a `Percentage of Row-Total`. I don't want to add new columns, then do the calculation, then delete the original column.
"ID" "CD" "6" "5" "4" "3" "1" "Count"
1 | RJ.1 | 45 | 124 | 53 | 75 | 5 | 302 |
2 | RJ.2 | 43 | 66 | 36 | 57 | 9 | 211 |
3 | RJ.3 | 29 | 104 | 41 | 76 | 8 | 258 |
4 | RJ.4 | 26 | 46 | 23 | 34 | 11 | 140 |
5 | RJ.5 | 34 | 51 | 41 | 22 | 13 | 161 |
6 | RJ.6 | 33 | 63 | 26 | 58 | 13 | 193 |
7 | RJ.7 | 22 | 56 | 40 | 41 | 8 | 167 |
8 | RJ.8 | 34 | 93 | 40 | 54 | 11 | 232 |
9 | RJ.9 | 34 | 69 | 34 | 50 | 11 | 198 |
10 | RJ.10 | 27 | 46 | 33 | 42 | 8 | 156 |
11 | RJ.11 | 32 | 70 | 26 | 42 | 15 | 185 |
12 | RJ.12 | 22 | 57 | 35 | 45 | 5 | 164 |
13 | RJ.13 | 25 | 83 | 39 | 43 | 9 | 199 |
14 | RJ.14 | 31 | 69 | 26 | 50 | 8 | 184 |
15 | RJ.15 | 24 | 62 | 39 | 35 | 10 | 170 |
16 | RJ.16 | 11 | 75 | 43 | 57 | 6 | 192 |
17 | RJ.17 | 21 | 45 | 21 | 23 | 8 | 118 |
18 | RJ.18 | 20 | 51 | 30 | 32 | 12 | 145 |
19 | RJ.19 | 28 | 60 | 34 | 37 | 9 | 168 |
20 | RJ.20 | 26 | 38 | 29 | 41 | 4 | 138 |
21 | RJ.21 | 21 | 64 | 23 | 42 | 11 | 161 |
22 | RJ.22 | 33 | 61 | 35 | 38 | 11 | 178 |
23 | RJ.23 | 19 | 51 | 36 | 36 | 8 | 150 |
24 | RJ.24 | 22 | 65 | 32 | 36 | 11 | 166 |
25 | RJ.25 | 28 | 85 | 35 | 45 | 11 | 204 |
26 | RJ.26 | 26 | 56 | 28 | 27 | 11 | 148 |
27 | RJ.27 | 29 | 43 | 41 | 33 | 7 | 153 |
28 | RJ.28 | 22 | 57 | 15 | 43 | 11 | 148 |
29 | RJ.29 | 14 | 30 | 22 | 35 | 9 | 110 |
30 | RJ.30 | 19 | 35 | 25 | 27 | 8 | 114 |
31 | RJ.31 | 18 | 30 | 28 | 26 | 12 | 114 |
32 | RJ.32 | 13 | 54 | 36 | 44 | 10 | 157 |
33 | RJ.33 | 20 | 59 | 19 | 40 | 9 | 147 |
34 | RJ.34 | 24 | 34 | 38 | 23 | 5 | 124 |
35 | RJ.35 | 26 | 67 | 37 | 39 | 8 | 177 |
36 | RJ.36 | 27 | 46 | 31 | 34 | 7 | 145 |
37 | RJ.37 | 24 | 26 | 27 | 36 | 13 | 126 |
38 | RJ.38 | 14 | 24 | 24 | 21 | 10 | 93 |
39 | RJ.39 | 31 | 58 | 34 | 28 | 10 | 161 |
40 | RJ.40 | 18 | 45 | 25 | 37 | 10 | 135 |
In one of my searches, someone had suggested that the values for multiple columns could be calculated in a faster way (avoid row level context evaluation) by merging the table with itself and with some M-code. But not shared the code example.
Is it possible?
Solved! Go to Solution.
Hello @Anonymous
here some dynamic solution. Uses Unpivoting, Grouping and twice transforming of columns. The only parameter you have to change is the list in the Unpivot other (in case you have some additional columns). As you can see you can also remove the column "count" from your table.
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VVRLdiQxCLtLr/PmGTD+XGGWs+3X979GCgmYZNFWUiVTIMl+v1/y+nr9+/snYPqziM5ndXuWHf/Hz4a+Pl/vl5IbMIOwVryMxfez3OenIqAaqQEaz2VE2Rmf2bHhxAs/4E5yAzTezVg0tlo8k9gkc4Dr5Hq9dKmyGm1J7JLFHha5aBLtWn3BT3OvgbvJ3VXI0cio4uCvDeoh9VQL14rq3a4aFbvk3uKu/stHj3Ypg4y0IlB3CYHWp1YTT2Mgl2+BFm/3aP2gBMw8TnY6B+R8Ud+8XAd5TZLTO6DGmwMvbtkOP+8lOc0DmtSIVHlU0ycrp3tA5GxpVUYvGF02rZb0D4g5EUi0gPbx/FJoSQOB2mlWqSihD0ml00KgjoqRjdISOslM8dJEoMaWNcpFq9zLYmlNE7XdsFNnAElCRCzJaSK7hHiz+qWJ8iPOWsdPO9BSwtlp9s7a6aIy5T1jH1mmiVJrughE/eWlha3/jTB7mjYCIcjxX2niERg0XdNGIHOxah9Snuc7204fgdqRg3SYGTuch1bTR9bqWEvH5Hfp9JH30SzDKaeXj0JFLH0ESgeUI+/OE0e09BGIULHyqYmZp2Knj9YXFu4N6DxnnwLnbWN1k1pn9VZLuHQ4TZLTRyDMZEpPpapueJDTRg4WX1+7Io0jiRF3Vk4XgT8uJ6lv7B8nxtJEoPa9jm2ME8xRxsnSRWa4O+cipUfe05Ym8s6It94XMfSmeHlkZroIhDGzXURzZD8KfD7f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, CD = _t, #"6" = _t, #"5" = _t, #"4" = _t, #"3" = _t, #"1" = _t, Count = _t]),
ChangeType = Table.TransformColumnTypes(Origine,{{"6", Int64.Type}, {"5", Int64.Type}, {"4", Int64.Type}, {"3", Int64.Type}, {"1", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(ChangeType,{"Count"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"ID", "CD"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"CD"}, {{"AllRows", each _, type table [ID=text, CD=text, Attribute=text, Value=number]}}),
TransformAllRows = Table.TransformColumns
(
#"Grouped Rows",
{
{
"AllRows",
(tbl)=>
let
SumValue = List.Sum(tbl[Value]),
TransformTbl = Table.TransformColumns
(
tbl,
{
{
"Value",
each _ / SumValue
}
}
)
in
TransformTbl
}
}
),
#"Expanded AllRows" = Table.ExpandTableColumn(TransformAllRows, "AllRows", {"ID", "Attribute", "Value"}, {"ID", "Attribute", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Expanded AllRows", List.Distinct(#"Expanded AllRows"[Attribute]), "Attribute", "Value", List.Sum),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"ID", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}})
in
#"Sorted Rows"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
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 |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |