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
I was tempted to generalize the following approach. For the first part it wasn't difficult.
But for the part where the type of the columns is specified, it wasn't easy.
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
But here is the result anyway.
A big thank to Ben Gribaudo now the solution is general and again one-linear
let
dim=1000000,
Origine = Table.FromColumns(List.Transform({1..10}, each List.Transform(List.Random(dim, _), each Number.Round(_*dim,0))),{"0".."9"}),
PercentOfRow = Table.FromRecords( Table.TransformRows(Origine, (r)=> let sum=List.Sum(Record.FieldValues(r)) in Record.FromList(List.Transform(Record.FieldValues(r), each _/sum) , {"0".."9"})& [Counts=sum]), Expression.Evaluate("type table"& "["&Text.Combine(List.Transform({"0".."9"}, each _&"="&"Percentage.Type"),",")&", Counts=number]",#shared))
in
PercentOfRow
Hi Anonymous ,
Any query in M can be written as one line this way, it is just a matter of time and persistence :).
This is similar, but parametrised approach:
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"}),
mTable = #"Removed Columns",
nonValueColumns = {"ID", "CD"},
fTransformRecord = (mRecord as record, mNonValueColumns as list)=>
let
recordsToProcess = Record.RemoveFields(mRecord, mNonValueColumns),
sumTotal = List.Sum(Record.ToList(recordsToProcess)),
TransformRecord = List.Accumulate(Record.FieldNames(recordsToProcess), recordsToProcess, (a, n)=> Record.TransformFields(a, {n, (x)=>x / sumTotal})) & Record.SelectFields(mRecord, nonValueColumns)
in TransformRecord,
Output = Table.FromRecords(Table.TransformRows(mTable, (x)=> fTransformRecord(x, nonValueColumns)))
in Output
Kind regards,
JB
Hi Anonymous ,
this statement of yours
"Any query in M can be written as one line this way, it is just a matter of time and persistence :)." (*)
is an incontrovertible truth of which I am well aware.
But it was not from this general aspect that my enthusiasm for the 1-line solution arose.
The purpose of my previous post, after seeing that Anonymous 's 1-line solution was performing, was to generalize it, for example, to be able to deal with a generic number of columns.
But, in a first attempt I was unable to make the part of setting column types parametric and in the same expression.
After reading Bengraud's blog, I was able, albeit with the use of the expression.evaluate function, to put in the same expression as it was in the 1-line solution of Anonymous also the part of setting column types.
from this two
PercentOfRow = Table.FromRecords( Table.TransformRows(Origine, (r)=> let sum=List.Sum(Record.FieldValues(r)) in Record.FromList(List.Transform(Record.FieldValues(r), each _/sum), {"0".."9"})& [Counts=sum])), pc = Table.TransformColumnTypes(PercentOfRow,List.Transform(Table.ColumnNames(Origine),each {_, Percentage.Type}))to this one
PercentOfRow = Table.FromRecords( Table.TransformRows(Origine, (r)=> let sum=List.Sum(Record.FieldValues(r)) in Record.FromList(List.Transform(Record.FieldValues(r), each _/sum) , {"0".."9"})& [Counts=sum]), Expression.Evaluate("type table"& "["&Text.Combine(List.Transform({"0".."9"}, each _&"="&"Percentage.Type"),",")&", Counts=number]",#shared))just this 🙂
(*)
the reduction to a single line was not simply incorporating one expression into another: so for example.
pc = Table.TransformColumnTypes( PercentOfRow = Table.FromRecords( Table.TransformRows(Origine, (r)=> let sum=List.Sum(Record.FieldValues(r)) in Record.FromList(List.Transform(Record.FieldValues(r), each _/sum), {"0".."9"})& [Counts=sum])),,List.Transform(Table.ColumnNames(Origine),each {_, Percentage.Type}))I would never have been thrilled with a solution put like this.
PS
In general I believe that it is not the task of power query to do these "aesthetic" operations, I mean to change the number type to percentage type.
Perhaps it is preferable to do only mash-up and not make-up.
This is fast. However, once you start adding additional calculated columns (see sample workbook & sample csv in TEST folder in my previous reply), then loading the table each time a calculated column gets added, goes for a toss! That is my concern and the desired output i am trying to achieve in a fast loading manner.