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.

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.