Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PhilippeMuniesa
Resolver I
Resolver I

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

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @PhilippeMuniesa 

 

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

e1.png

 

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:

e2.png

 

Best Regards

Allan

 

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

View solution in original post

4 REPLIES 4
v-alq-msft
Community Support
Community Support

Hi, @PhilippeMuniesa 

 

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

e1.png

 

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:

e2.png

 

Best Regards

Allan

 

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

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

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
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors