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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Yonah
Helper II
Helper II

Sumifs without group by

Hi,
I'm going crazy, I'm trying to add a new column in Powerquery that works similar to Excel as Summewenns. The important thing is, it should work without
grouping.

My table, looks like this

Article numberSome other colums x1-x33Invoice No.Item number-returnsInvoice number-returnsTurnonerNEW Column
1 20120100350
2 30230150150
3 403302000
1 20230250150

 

The column Turnoner is to be added Criteria area 1 is the column Article number with the creterium which stands in Item number-returns, the second riterien area stands in Invoice No. whereby the creterium stands in Invoice number-returns.


Thanks

 

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

=let a=Table.Buffer(Table.Group(PreviousStepName,{"Article number","Invoice No."},{"n",each List.Sum([Turnover])})) in Table.AddColumn(PreviousStepName,"NewColumn",each a{[#"Article number"=[#"Item number-returns"],#"Invoice No."=[#"Invoice number-returns"]]}?[n]? ??0)

View solution in original post

3 REPLIES 3
wdx223_Daniel
Super User
Super User

=let a=Table.Buffer(Table.Group(PreviousStepName,{"Article number","Invoice No."},{"n",each List.Sum([Turnover])})) in Table.AddColumn(PreviousStepName,"NewColumn",each a{[#"Article number"=[#"Item number-returns"],#"Invoice No."=[#"Invoice number-returns"]]}?[n]? ??0)

Yonah
Helper II
Helper II

This is only for own if, but I need it for two ifs. 

Poohkrd
Advocate I
Advocate I

Hi, that's it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIAYiMDIGEIZxiASGNTA6VYnWglI6gaY5CgEYxhaAojQWqMoWpMwBrhisHmQFSg2AQ3xQhhSiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Article number" = _t, #"Some other colums x1-x33" = _t, #"Invoice No." = _t, #"Item number-returns" = _t, #"Invoice number-returns" = _t, Turnoner = _t, #"NEW Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Turnoner", type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Article number"}, #"Changed Type", {"Article number"}, "Source", JoinKind.LeftOuter),
    #"Aggregated {0}" = Table.AggregateTableColumn(#"Merged Queries", "Source", {{"Turnoner", List.Sum, "Sum Turnoner"}})
in
    #"Aggregated {0}"

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors