PowerQuerry: kumulierter Wert auf Zeilenbasis

Forumthread: PowerQuerry: kumulierter Wert auf Zeilenbasis

21.01.2025 11:20:15

Juma2025

Hallo liebe Excelanwender,

ich möchte gerne in Power Querry die Werte Zeilenweise kumulieren lassen.

Wie stelle ich das an?

Beispiel:

Userbild

In Spalte A sind verschiedene Produkte
In Spalte B der dazugehörige Umsatz

Ich möchte jetzt eine neue Spalte in Power Querry hinzufügen die mir die Umsätze nach und nach kumuliert --> in meinem Beispiel wäre es die Spalte C.

Also in C3 soll B2+B3 sein, C4= B2+B3+B4, usw.

Habt ihr eine Idee?

Vielen Dank schon Mal
Beste Grüße
Mat

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Im Prinzip...

21.01.2025 11:47:06

Case
AW: Im Prinzip...

21.01.2025 12:27:19

Juma2025
AW: Im Prinzip...

21.01.2025 12:42:04

Yal
AW: Im Prinzip...

21.01.2025 12:47:10

Juma2025
AW: Im Prinzip...

21.01.2025 13:09:07

Yal
In dem Fall...

21.01.2025 12:42:46

Case
AW: In dem Fall...

21.01.2025 12:50:20

Juma2025
Dann würde ich...

21.01.2025 14:11:05

Case
AW: In dem Fall...

21.01.2025 15:22:58

Yal
AW: Dann würde ich...

21.01.2025 14:18:43

Juma2025
Dann würde ich auf...

22.01.2025 10:57:33

Case

Im Prinzip...

21.01.2025 11:47:06

Case

Moin, :-)

... geht das über einen Index und über "List.Sum(List.FirstN...": ;-)
https://www.herber.de/bbs/user/174995.xlsx

Die Zeile ist also:

HinzuBS = Table.AddColumn(LetzterSchritt, "Kumuliert", each List.Sum(List.FirstN(LetzterSchritt[Umsatz], [Index])))

Servus
Case

AW: Im Prinzip...

21.01.2025 12:27:19

Juma2025

Hi Case,

danke für deine schnelle Rückmeldung.

Ich habe es hinbekommen aber leider ist die Ladezeit super lange wenn ich die Daten in Excel laden möchte.

Ich habe ca. 27.000 Zeilen. Wenn ich direkt in Excel kumuliere dann dauert es nur ein Bruchteil. Kann man das irgendwie beschleunigen?

Vielen Dank und beste Grüße
Mat

AW: Im Prinzip...

21.01.2025 12:42:04

Yal

Hallo Mat,

jein. Die Kumulus-Berechnung ist tatsächlich eine der Stellte, wo Power Query nicht ganz auf der Hohe ist.

Was machbar und schnell sein könnte, wäre die Daten über PQ zu laden und diese an einem Pivottable zu übergeben, worin der Kumul gemacht wird.
Ich könnte mich vorstellen, dass dies der Mittelweg zwischen Flexibilität und Performance ist.

VG
Yal

AW: Im Prinzip...

21.01.2025 12:47:10

Juma2025

Hi Yal,

das klingt nach ner Interessanten Idee,

das heißt ich übergebe die Wert an eine Pivot TAbelle und lade Sie danach wieder in Power Querry um mit den Werten weiter zu arbeiten?

Das kann aber auch automatisch gemacht werden oder?

Wie würde das funktionieren?

Vielen Dank und beste Grüße
Mat

AW: Im Prinzip...

21.01.2025 13:09:07

Yal

Hallo Mat,

nicht ganz: Du lädst die Daten aus der Datei mit Power Query. Deine Abfrage ist dann fertig und Du kannst das Ergebnis davon in einem PivotTable visualisieren.
Aber innerhalb von Power Query kannst Du auch die Abfrage selbst als Quelle verwenden.

Problem wäre, wenn Du die jeweilige kumulierte Werte für eine weitere Berechnung verwenden müsste, weil diese nur ausserhalb von PQ existieren. Da musste man sich die Frage stellen, ob es notwendig ist, diese Berechnung für jede einzelne Zeile im Voraus zu haben.

Folgende Links könnten helfen:
- Create running totals (4 methods): https://gorilla.bi/power-query/running-total/
- Apply running total by category: https://gorilla.bi/power-query/running-total-by-category/
- Create running totals in bulk: https://gorilla.bi/power-query/creating-multiple-running-totals/

VG
Yal

In dem Fall...

21.01.2025 12:42:46

Case

Moin, :-)

... ist es besser "List.Accumulate" zu verwenden und die Tabelle zu "puffern": ;-)

Puffer = Table.Buffer(LetzterScritt),

HinzuS = Table.AddColumn(Puffer, "Kumuliert", each List.Accumulate(List.FirstN(Puffer[Umsatz], [Index]), 0, (state, current) => state + current), type number)

https://learn.microsoft.com/de-de/powerquery-m/table-buffer
https://learn.microsoft.com/en-us/powerquery-m/list-accumulate

Servus
Case

AW: In dem Fall...

21.01.2025 12:50:20

Juma2025

Hi Case,

das hatte ich tatsächlich auch schon probiert. ChatGPT hat mir da ne Funktion ausgespuckt. Die Accumulate Funktion dauert genauso lange und kombiniert mit dem Puffer erhalte ich die Fehlermeldung dass ich nicht genug Arbeitsspeicher habe :-) Ich denke aber 32Gb sollten ausreichen.

Mat

Dann würde ich...

21.01.2025 14:11:05

Case

Moin, :-)

... mir überlegen, ob man nicht "Blöcke" bilden kann. Ist denn ein kumulieren für jede Zeile nötig?

Servus
Case

AW: In dem Fall...

21.01.2025 15:22:58

Yal

Moin zusammen,

zum Thema "Blocks"...

Ich habe einen Test gemacht, jedoch auf so wenige Daten, dass ich nicht garantieren kann, dass es performant ist:
- ich habe eine Tabelle mit Datum, Kategorie und Wert,
- ich extrahiere aus dem Datum das Jahr in eine separaten Spalte,
- die Daten werden nach Kategorie und Jahr in einer separate Tabelle gruppiert, Gruppierung "Alle Daten"
- die gruppierte Datensätze werden zu den originale gejoint ("Abfrage zusammeführen"), dann hat man pro originale Zeile eine Grp-Tabelle mit Datum und Wert
- in den gejointe Tabelle wird eine neue Spalte eingeführt, die die Summe der grp-Tabelle-Spalte "Wert" in den Zeile wo das Datum = orig.Datum erzeugt
- anschliessend unnötige Spalten gelöscht

https://www.herber.de/bbs/user/175004.xlsx

VG
Yal

AW: Dann würde ich...

21.01.2025 14:18:43

Juma2025

Hi Case,

leider ja...mit der Spalte muss ich dann eine neue Spalte generieren, wo die Prozente davon angezeigt werden sollen. Ziel ist es dann in einer weiteren Spalte alle Zellen zu markieren, die zu den Top 80% gehören.

Dann würde ich auf...

22.01.2025 10:57:33

Case

Moin, :-)

... VBA zurückgreifen: ;-)
https://www.herber.de/bbs/user/175026.zip

Da ist auch eine Formel drin, welche die neuen (Excel 365) Funktionen nutzt. Spill-Formeln kannst du aber nicht in ListObjects nutzen.

Vielleicht kannst du sie brauchen.

Servus
Case

PowerQuerry: kumulierter Wert auf Zeilenbasis

Forumthread: PowerQuerry: kumulierter Wert auf Zeilenbasis

11 Beiträge zum Forumthread Beiträge zu diesem Forumthread

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread