Forum Discussion
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 number | Some other colums x1-x33 | Invoice No. | Item number-returns | Invoice number-returns | Turnoner | NEW Column |
| 1 | 20 | 1 | 20 | 100 | 350 | |
| 2 | 30 | 2 | 30 | 150 | 150 | |
| 3 | 40 | 3 | 30 | 200 | 0 | |
| 1 | 20 | 2 | 30 | 250 | 150 |
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
=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)
3 Replies
=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)
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}"This is only for own if, but I need it for two ifs.
