cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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

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"

1RJ.14512453755302
2RJ.2436636579211
3RJ.32910441768258
4RJ.42646233411140
5RJ.53451412213161
6RJ.63363265813193
7RJ.7225640418167
8RJ.83493405411232
9RJ.93469345011198
10RJ.10274633428156
11RJ.113270264215185
12RJ.12225735455164
13RJ.13258339439199
14RJ.14316926508184
15RJ.152462393510170
16RJ.16117543576192
17RJ.17214521238118
18RJ.182051303212145
19RJ.19286034379168
20RJ.20263829414138
21RJ.212164234211161
22RJ.223361353811178
23RJ.23195136368150
24RJ.242265323611166
25RJ.252885354511204
26RJ.262656282711148
27RJ.27294341337153
28RJ.282257154311148
29RJ.29143022359110
30RJ.30193525278114
31RJ.311830282612114
32RJ.321354364410157
33RJ.33205919409147
34RJ.34243438235124
35RJ.35266737398177
36RJ.36274631347145
37RJ.372426273613126
38RJ.38142424211093
39RJ.393158342810161
40RJ.401845253710135

 

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?

24 REPLIES 24
Highlighted

Hi @sifar786 

 

the csv and xlsx files you uploaded are not clear to me.

 

the files you uploaded are not clear to me.
And above all, it is not clear what gb's are.
I have already showed you with code that the operations you described (including those with percentiles) in a table of 1000000 rows and 10 columns are performed in a few minutes.
 
PS
In general, avoid making M do unnecessary operations such as rounding and other aesthetic things.
You can do these in a different environment or at the end.
 
Highlighted

@Rocco_sprmnt21 oh, sorry for the confusion.

GB's  = Gigabytes...that's what shows up when the workbook is connecting to the Datasource i.e. Sample CSV when i am adding additional columns as shown in the `Desired output`.

 

What i meant is, given a sample csv file, how do i transform this csv into the `Desired Output` without increasing the load time when i refresh the queries? The output is pretty straight-forward and all the column formulae are shown.

 

If it's possible, can you revisit and have a look at the workbook? I am particularly interested in the suggestion you made earlier i.e. to calculate the columns separately and then club them together. Can you help here?

Highlighted

Since the csv and xlsx files you uploaded are not clear to me, I use a randomly created tables (5k rows)  to explain an idea of what can happen in your case (but without having a precise view of the input data and the code you use, I can just guess what happens).


Run the two different scripts and note the differences.

If you find any differences, use the scheme that best suits your needs.

 

let
    dim=50000,
    Origine = Table.FromColumns(List.Transform({1..10}, each List.Transform(List.Random(dim, _), each Number.Round(_*dim,0))),{"0".."9"}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(Origine, "total", each [4]+[6]),
    avg=List.Average(#"Aggiunta colonna personalizzata"[total]), 
    acp1 = Table.AddColumn(#"Aggiunta colonna personalizzata", "pctot", each [total]/avg),
    pcn=pcntl(acp1[pctot],0.8),
    acp = Table.AddColumn(acp1, "pcntl", each if [pctot]/pcn>1 then "H" else "")
in
    acp

 

 

 

 

let
    dim=50000,
    Origine = Table.FromColumns(List.Transform({1..10}, each List.Transform(List.Random(dim, _), each Number.Round(_*dim,0))),{"0".."9"}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(Origine, "total", each [4]+[6]),
    acp1 = Table.AddColumn(#"Aggiunta colonna personalizzata", "pctot", each [total]/List.Average(#"Aggiunta colonna personalizzata"[total])),
    acp = Table.AddColumn(acp1, "pcntl", each if [pctot]/pcntl(acp1[pctot],0.8)>1 then "H" else "")
in
    acp

 

 

pcntl(list, number) is the your percentile function.

 

 

 

Highlighted
Helper V
Helper V

Have you tried just transforming the columns?

 

Table.TransformColumns(PriorStep, {{"6",each _/[Count]}, {"5", each _/[Count]}})

 

No reason that shouldn't work for ya. 

Highlighted

Hi @watkinnc,

 

Thanks for your reply. Could you look at the sample files that i had shared earlier and see if you could recreate the Output sheet table from the given sample file?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors