Forum Discussion

Yonah's avatar
Yonah
Helper II
3 years ago
Solved

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 ...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    =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)

Poohkrd's avatar
Poohkrd
Advocate I
3 years ago

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}"