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

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.

let
    dim=500000,
    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})),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(pc, "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 [pctot]/pcn)
in
    acp

this om my laptop ends in few minutes

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 

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

I'm not sure I understand what you want to do, but maybe it can be useful to try doing separate calculations on the same source table and then putting it all together.

ac1 = func1 (tab),
ac2 = func2 (tab),
..

tfc=Table.FromColumns(

Wow! This is incredible.

Anonymous, I created a `sample workbook` and the `sample input csv` showing the Input and the desired Output just for your experienced eyes to see why the data is loading so slowly. I have added the percentile function that i had googled earlier (it seems there is no inbuilt functions in PQ for Percentile, Rank etc.)

Here are the files in this TEST folder. I have removed adding the other Columns code (as shown in desired output) as it was taking a long time to finish and just kept the code till where the row percentage of total gets calculated. The calculations for the additional columns are shown in the desired output table.

I am not sure what you meant by creating the columns separately and then joining them. I hope you can provide some clear logic on how to speed up the loading once the entire desired output report gets created.

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.

Anonymous 

---

Anonymous wrote:

I'm not sure I understand what you want to do, but maybe it can be useful to try doing separate calculations on the same source table and then putting it all together.

ac1 = func1 (tab),
ac2 = func2 (tab),
..

 

tfc=Table.FromColumns(

---

These seem useful, but don't know how to do it without affecting loading time. How would you do it with my given sample files to create the desired output as shown in the workbook in a speedier way? Right now, in my amateur way, the loading takes a lot of time (going into gb's).

Anonymous 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?

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.