Faster Division/Multiplication using Merge same table Query - Power Query

[Anonymous]

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?

[Anonymous]

A more coincise and (peraphs ) general way to write "your" code:

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])),
    pc = Table.TransformColumnTypes(PercentOfRow,List.Transform(Table.ColumnNames(Origine),each {_, Percentage.Type}))
in
    pc

[Jimmy801]

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

[edhans]

Hi @Anonymous - oddly enough I do not think you can reference another column within a Table.TransformColumn. I've not see a way to do it.

However, you can use the Table.ReplaceValue function. See below - this is just for column 6. You'd need to repeat for each column.

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 [DI = _t, CD = _t, #"6" = _t, #"5" = _t, #"4" = _t, #"3" = _t, #"1" = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"6", Int64.Type}, {"5", Int64.Type}, {"4", Int64.Type}, {"3", Int64.Type}, {"1", Int64.Type}, {"Count", Int64.Type}}),
    #"Replaced Value" = 
    Table.ReplaceValue(
        #"Changed Type",
        each
        let
            varCurrentValue = [6]
        in
            varCurrentValue,

            each [6] / [Count],
            Replacer.ReplaceValue,{"6"})
in
    #"Replaced Value"

Here is what it does:

• It assigns the current value of column 6 to the varCurrentValue variable. (current value is whatever it is for each row)
• It searches for the current value.
• Then it replaces it with [6] / [Count]

I did test this with 2 records that had the same value (45) but different values in the Count column for that row, and it worked.

The first value was 45, and now divided by 302 gives the .149 value.

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.

[Anonymous]

It's not clear why you don't want to do row-level transformations.
Nothing comes to mind about how to use the Table.Join functions to do these transformations (considering that these functions do not expose parameters which are functions that act on the rows / columns of the tables). You should provide the exact references of the post where you found these tips.
In the meantime, I can offer you this "strange" solution that makes use of the pivot and unpivot functions.

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]),
    #"Rimosse colonne" = Table.RemoveColumns(Origine,{"ID","Count"}),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Rimosse colonne",{{"6", Int64.Type}, {"5", Int64.Type}, {"4", Int64.Type}, {"3", Int64.Type}, {"1", Int64.Type}}),
    #"Trasformate altre colonne tramite UnPivot" = Table.UnpivotOtherColumns(#"Modificato tipo", {"CD"}, "Attributo", "Valore"),
    #"Rimosse colonne1" = Table.RemoveColumns(#"Trasformate altre colonne tramite UnPivot",{"Attributo"}),
    #"Colonna trasformata tramite Pivot" = Table.Pivot(#"Rimosse colonne1", List.Distinct(#"Rimosse colonne1"[CD]), "CD", "Valore", (v)=>List.Transform(v, each _/List.Sum(v))),
    #"Trasposta colonna" = Table.Transpose(#"Colonna trasformata tramite Pivot"),
    #"Valori estratti" = Table.TransformColumns(#"Trasposta colonna", {"Column1", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Valori estratti", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"Column1.1", type number}, {"Column1.2", type number}, {"Column1.3", type number}, {"Column1.4", type number}, {"Column1.5", type number}})
in
    #"Modificato tipo1"