Forum Discussion

PhilippeMuniesa's avatar
PhilippeMuniesa
Resolver I
5 years ago
Solved

PowerQuery Equivalent of sum.if nb.if

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

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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's avatar
      PhilippeMuniesa
      Resolver I
      5 years ago

      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

    • PhilippeMuniesa's avatar
      PhilippeMuniesa
      Resolver I
      5 years ago

      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

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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