M v Power Query – kumulativní součty za skupinu do nového sloupce

Tento návod popisuje, jak v prostředí Power Query zjistit do nového sloupce součty za celou skupinu.

Např. v této tabulce chceme ke každému druhu zvířete přiřadit součet všech zvířat tohoto druhu.

V Excelu by vám pomohla funkce SUMIFS a v DAXu tento postup, ale v Power Query na to musíme jinak.

Vyjdeme z této tabulky:

Pak seskupíme a při seskupení vysčítáme tabulku podle zvířat:

Teď jsme ve zvláštní situaci, kdy potřebujeme tabulku jakoby připojit samu na sebe. Tedy k tabulce před seskupením bychom potřebovali připojit tu samou tabulku už seskupenou. Tedy připojit na sebe dva kroky jednoho dotazu.

To zní hodně punkově, ale jde to. Buď to můžeme napsat přímo v M, ale lepší bude klikat.

Půjdeme na poslední krok (po seskupení) a připojíme ho “na sebe”:

To je samo o sobě k ničemu. Teď už ale stačí přepsat nově vytvořené připojení tak, aby se nepřipojovalo k výsledku předchozího kroku, ale k dotazu ještě před seskupením:

Pro rozbalení přiřazeného sloupce jsme hotovi:

Pro lepší pochopení je dobré podívat se na kód. V běžných dotazech to funguje tak, že každý krok (proměnná) vznikne tak, že zpracuje krok předchozí, pomocí nějaké funkce. Kousek kódu může vypadat třeba takto. Pro snadnější čtení jsem probarvil názvy kroků náhodnými barvičkami:

  • #”Changed Type” = Table.TransformColumnTypes(Source,{{“Sample”, Int64.Type}}),
  • #”Filtered Rows” = Table.SelectRows(#”Changed Type”, each ([Sample] <> 2)),
  • #”Inserted Multiplication” = Table.AddColumn(#”Filtered Rows“, “Multiplication”, each [Sample] * 2, type number),
  • #”Kept First Rows” = Table.FirstN(#”Inserted Multiplication”,4)

V našem kódu jsme to ale trochu zpřeházeli, takže třetí řádek se odkazuje na oba předchozí současně. Neobvyklé, ale občas, jako tady, dost zajímavé.

  • #”Changed Type” = Table.TransformColumnTypes(Source,{{“Animal”, type text}, {“Color”, type text}, {“Number of items”, Int64.Type}}),
  • #”Grouped Rows” = Table.Group(#“Changed Type”, {“Animal”}, {{“Total number for this animal”, each List.Sum([Number of items]), type nullable number}}),
  • #”Merged Queries” = Table.NestedJoin(#”Changed Type”, {“Animal”}, #”Grouped Rows”, {“Animal”}, “Grouped Rows”, JoinKind.LeftOuter),

Jak to udělat jednodušším způsobem?

Kumulativní součty můžete udělat i tak, že hodnoty seskupíte podle nadřazené skupiny. Jedna agregace bude např. součet, a druhá výpis všech původních řádků (taková agregace neagregace…).

Potvrdíte, rozkliknete:

a máte také výsledek: