Forum Discussion
Faster Division/Multiplication using Merge same table Query - Power Query
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
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
Hi Jimmy801 ,
I found this solution last night which is pretty fast and consice:
let
Source = 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, Counts = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"6", Int64.Type}, {"5", Int64.Type}, {"4", Int64.Type}, {"3", Int64.Type}, {"1", Int64.Type}, {"Counts", Int64.Type}}),
PercentOfRow = Table.FromRecords(
Table.TransformRows(
#"Changed Type",
(r) =>
Record.TransformFields(
r,
{
{"6", each r[#"6"] / r[Counts] },
{"5", each r[#"5"] / r[Counts] },
{"4", each r[#"4"] / r[Counts] },
{"3", each r[#"3"] / r[Counts] },
{"1", each r[#"1"] / r[Counts] }
}
)
),
type table [
CD= Text.Type,
ID = Int64.Type,
#"6" = Percentage.Type,
#"5" = Percentage.Type,
#"4" = Percentage.Type,
#"3" = Percentage.Type,
#"1" = Percentage.Type
]
)
in
PercentOfRow
taken from here:
hello Anonymous
i wanted propose you this solution, but it's not dynamically and you need the counts-column to make it nice. therefore I opted for the TransformColumns-solution
BR
Jimmy
Hi Anonymous
the solution you found works at the row level, which is just what it seemed you asked not to do.
If you are also interested in this type of solution, it is also possible to make it usable in general and not only for 5 columns with a given name.
I found Anonymous solution quite innovative with the calculations done in lists. However, the solution loses other main columns like CD, ID etc and you have to again rename the Attrb columns.
Jimmy801 solution was straight forward with the unpivot - pivot and calculations though it took the same amount of time as my solution.
The problem that i am facing is that i am adding additional calculated columns to this final table and at each step that references previous calculated step, the loading just increases exponentially into Gigabytes though only for these 40 rows.
for e.g.
1. Step1 = Table.AddColumn(Step0, each [6] / Average[6]) then
2. Step2 = Table.AddColumn(Step1, each [4] / Average[4]).
But that will be another post that i will put up today.
Hi Anonymous
" ...
I found @Rocco_sprmnt21 solution quite innovative with the calculations done in lists. However, the solution loses other main columns like CD, ID etc and you have to again rename the Attrb columns.
..."
I have tried, from what can be understood from the request, to stay strictly on the subject and give a hopefully useful suggestion.
I haven't tried to do a school homework with all the things in the right place: I don't have enough time to do this right now.
If from a performance point of view my solution is preferable to the others that work at the row level, you could fill in the missing aspects.