PowerQuery Equivalent of sum.if nb.if

[PhilippeMuniesa]

hello,

I'm looking to create columns with equivalents of sum.if and nb.if

the same as the table as an example, where only the first two columns are known at the beginning

REF Montant nbref countrow sumif
A     100               2             3     300
B      150              3             2      300
E       100             1             3      100
C       120             1             1      100
A      150              2             2       300
B      100              3             3       300
B      180              3             1       180

Thank you for the help you can give me

Philippe Muniesa

[v-alq-msft · Accepted solution]

Hi, @PhilippeMuniesa 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

You may paste the following codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YlWcgKxTSFsVyRxZxDbCMJ2RFLjhK7XAsiOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [REF = _t, Montant = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"REF", type text}, {"Montant", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "nbref", each let ref=[REF] in
Table.RowCount(Table.SelectRows(#"Changed Type",each [REF]=ref))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "countrow", each let m=[Montant] in
Table.RowCount(Table.SelectRows(#"Added Custom",each [Montant]=m))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "sumif", each [Montant]*[countrow])
in
    #"Added Custom2"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilippeMuniesa]

Hello Allan,

I return to the previous topic for which you gave me a solution (sum.if & nb.if) .

I implemented this solution on my real basis thinking that it would be more effective than the Group By and the creation of intermediate tables, and then their subsequent merger that I had implemented.

My table has 201878 lines of which many duplicates (the "Montant" duplicates represent 159072 lines).

I find that at the executions of the first line.
"Added Custom1" - Table.AddColumn ("Added Custom," "countrow," each let m[Montant] in
Table.RowCount (Table.SelectRows), the execution is not over. As I write, the powerquery editor of excel, is in the process of updating the preview, and it indicates a 7.7GB download of data.... and never ends.

Can you tell me if you think it is normal, and if I have to abandon this method on large tables. Thank you again for all your help.

Philippe Muniesa

[PhilippeMuniesa]

Thanks a lot, it s exactly that i want

I had done this by creating successive tables with the "group by" function, and merging the different tables obtained, but it's much more complicated, and I think it degrades the processing times. I'm going to adapt my code.

Thanks again

Philippe

[Fowmy]

@PhilippeMuniesa 

How do you calculate the countrow value?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn