PowerQuery Berechnung

[Tino Jenke]

Hallo zusammen,

ich habe da mal ein "komplexeres" Problem an dem ich mir aktuell die Zähne ausbeiße.

Ich habe eine PowerQuery Abfrage die sich Daten aus SmapOne zieht. Dabei geht es um eine Baukasse. Ich habe die Anfrage schon soweit formatiert wie ich das benötige. Nun fehlt mir nur noch eine Berechnung. Die Abfrage hat die Spalten "AlterKontostand", "Einnahme" und "Ausgabe". Ich versuche jetzt schon seit Tagen mit diversen KI Tools folgendes zu erreichen. Nur in Zeile 1, soll die folgende Berechnung stattfinden:

AlterKontostand + Einnahme - Ausgabe. Das Ergebnis soll dann in eine neu erzeugte Spalte "Ergebnis" geschrieben werden. Woran es jetzt scheitert ist alles ab Zeile 2. Ab da soll dann nicht mehr "AlterKontostand" der Ausgang der Berechnung sein sondern das Ergebnis der Zeile darüber. Also in Zeile 2 Ergebnis aus Zeile 1 + Einnahme - Ausgabe = Ergebnis usw. Es wäre auch in Ordnung das ganze mit irgendwelchen Zusätzlich Spalten zu regeln, das kann ich mir dann zurecht schieben.  Hat da eventuell schon mal jemand etwas gebaut oder schon Erfahrung damit?

Ich danke euch ganz sehr im Voraus 🙏

[Micha Zenke]

Hallo @Tino Jenke,

ich bin mir noch nicht sicher ob ich deinen fall richtig verstanden hab 😄.

Mein Vorschlag wäre folgender:

Du trägst in Zelle [E2] einen Kontostand alt ein, mehr muss in Zeile 2 nicht stehen.

Die Zeile 2 kannst du ja dann wenn du wolltest auch ausblenden.

Und in Zelle [B3] gibst du als Formel ein =E2 und sast auf alle Zeilen Übernehmen;

in Zelle [E3] sagst du =B3+C3-D3

 

Ist das in etwa das was du gesucht hast oder hab ich dich komplett falsch verstanden

Gruß Micha

[Tino Jenke]

Hallo Micha,

Danke für deine Antwort aber ich fürchte ja, mir geht es wirklich um Power Query und den M-Code dahinter. Mit Excel Formeln komme ich da leider nicht weiter.

Edited May 6 by Tino Jenke

[Bülent Erbas]

Hi Tino,

das gibt ChatGPT aus. Ich vermute aber das kennst du schon.

>>
Hier wird eine kumulative Berechnung durchgeführt, bei der das "Ergebnis" in jeder Zeile auf dem Ergebnis der vorherigen Zeile basiert. Die erste Zeile verwendet dabei den "AlterKontostand" für die Berechnung, und für die folgenden Zeilen wird jeweils das zuvor berechnete Ergebnis verwendet.

So kannst du das umsetzen:

= Table.AddColumn(
    DeineTabelle, 
    "Ergebnis", 
    (row) => 
    let
        previousRow = if row[Index] = 1 then row[AlterKontostand] else row[Einnahme] - row[Ausgabe] + row[ErgebnisVorher]
    in
        previousRow + row[Einnahme] - row[Ausgabe]
  )
```

In dieser Formel:
- `DeineTabelle` sollte durch den tatsächlichen Namen deiner Tabelle ersetzt werden.
- Es wird angenommen, dass du bereits eine Spalte `Index` hast, die die Zeilennummern angibt. Wenn du keine Index-Spalte hast, kannst du diese einfach hinzufügen durch:
  - Hinzufügen einer Index-Spalte von 1 an beginnend (Start Index bei 1).

Die Formel für die benutzerdefinierte Spalte verwendet bedingte Logik (`if`) um zu prüfen, ob es sich um die erste Zeile handelt (indiziert durch `row[Index] = 1`). Für die erste Zeile nimmt es den `AlterKontostand`, und für alle weiteren Zeilen summiert es das Ergebnis der vorherigen Zeile mit `Einnahme` und subtrahiert `Ausgabe`.
<<

[Tino Jenke]

@Bülent Erbas

Ja das hatte mir ChatGPT in 100 verschiedenen Facetten ausgegeben. 😅 Leider hat mich keine davon weiter gebracht.

Ich konnte es jetzt über diverse Umwege lösen können mit Vielen Teilbausteinen von Chat GPT.

Die Lösung sieht jetzt in meinem Szenario so aus:

 

#"Hinzugefügter Index" = Table.AddIndexColumn(#"Entfernte Spalten", "Index", 1, 1, Int64.Type),
    Berechnung1 = Table.AddColumn(#"Hinzugefügter Index", "Summe", each 0 + [Einnahme] - [Ausgabe]),

 
    // Annahme: Quelle ist der Name deiner Tabelle/Abfrage
    Quelle1 = Berechnung1,

    // Schritt 1: Lade den Wert des Jahresanfangs aus einer anderen Abfrage
    JahresanfangQuery = Jahresanfang,// Hier den Code einfügen, um den Wert des Jahresanfangs zu erhalten
    
    // Schritt 2: Schleife über die Zeilen der Spalte ausführen und die Werte aufsummieren
    AddCustomColumn = Table.AddColumn(Quelle1, "Kumulierte Summe", each
        let
            // Aktuelle Zeile
            currentRow = _,
            // Index der aktuellen Zeile
            currentIndex = currentRow[Index],
            // Summe der Werte bis zur aktuellen Zeile
            cumulativeSum = List.Sum(List.FirstN(Quelle1[Summe], currentIndex)) + JahresanfangQuery // Hier den Wert des Jahresanfangs einfügen
        in
            cumulativeSum
    ),

    NeuerAlterKontostand = Table.AddColumn(AddCustomColumn, "Alter Kontostand", each if
    [Index] = 1 then [AlterKontostand] else null),
    #"Entfernte Spalten3" = Table.RemoveColumns(NeuerAlterKontostand,{"AlterKontostand", "Index", "Summe"}),
    #"Neu angeordnete Spalten2" = Table.ReorderColumns(#"Entfernte Spalten3",{"Name", "Alter Kontostand", "Buchungsdatum", "Buchungstext", "Kommissionsnummer", "Einnahme", "Ausgabe", "FileID", "RecordID", "smapId", "Kumulierte Summe"}),

    step1 = {null} & List.RemoveLastN(Table.Column(#"Neu angeordnete Spalten2", "Kumulierte Summe"),1),
step2 = Table.ToColumns(#"Neu angeordnete Spalten2") & {step1},
    ErzeugePreviousRow = Table.FromColumns(step2, Table.ColumnNames(#"Neu angeordnete Spalten2") & {"previousRow"}),
    #"Geänderter Typ1" = Table.TransformColumnTypes(ErzeugePreviousRow,{{"Kumulierte Summe", Currency.Type}, {"Alter Kontostand", Currency.Type}, {"previousRow", Currency.Type}}),
    #"Zusammengeführte Spalten" = Table.CombineColumns(Table.TransformColumnTypes(#"Geänderter Typ1", {{"previousRow", type text}, {"Alter Kontostand", type text}}, "de-DE"),{"previousRow", "Alter Kontostand"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"AlterKontostand"),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Zusammengeführte Spalten",{{"AlterKontostand", Currency.Type}}),

 

Ganz grob gesagt funktioniert es so, dass die Berechnung erstmal mit Ausgangswert 0 in jeder Zeile durchgeführt wird. Also 0 + Einnahme - Ausgabe. Daraus wird eine Summe gebildet. Dieses Ergebnis wird dann in die Spalte Summe geschrieben. Es wird dann vom 1. Ausgangswert (in Meinem Fall der Jahresanfang) angefangen die Summe der Zeile zum Kontostand hinzu zu addieren. Dadurch entsteht die Kumulierte Summe, die dann einfach über einen Feldbefehl eine Zeile nach unten versetzt wird und so wieder den Basiswert AlterKontostand bildet.

 

Habe mir die Zähne daran ausgebissen, aber für mich hat es so funktioniert. Ich hoffe mit der Erklärung lässt sich das für irgendjemanden bei Bedarf replizieren.

 

VG Tino 😊