Office: (Office 365) Bedingte Summierung in PQ

Hallo zusammen,

ich habe folgendes Problem:

Spalte A: Datum
Spalte B: Gewichte

Pro Tag wird in Spalte B ein Gewicht geschrieben. Solange jeden Tag in Spalte B ein Gewicht geschrieben wird, möchte ich, dass die Gewichte automatisch summiert werden. Solange bis wieder ein Tag kommt an dem das Gewicht 0 ist. Sobald wieder ein Gewicht geschrieben wird, soll die Summierung neu beginnen...usw.

Beispieldaten finden sich im Anhang. Ich möchte dies in PQ umsetzen.

Bitte um eure Hilfe.

Danke

 

Moin Hannes,

mit PQ kann ich leider nicht dienen, aber mit wenigen Zeilen VBA, die Du einfach in Deine Tabelle einfügst (Rechtsklick auf den Tabellennamen -> Code anzeigen -> Code einkopieren -> Code-Fenster schließen(X oben rechts) -> Datei speichern.

Code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    'wird beim Wechsel in eine andere Zelle gestartet
Dim i As Integer, r As Integer, sgSum As Single                 'Deklaration der Variablen

r = Cells(Rows.Count, 1).End(xlUp).Row  'bestimmt die letzte belegte Zeile in Spalte A (1)

For i = 1 To r                          'Schleife durch alle belegte Zeilen
    sgSum = sgSum + Cells(i, 1)         'addiert den Wert der aktuellen Zelle zur Summe
    If Cells(i, 1) = 0 Then             'prüft ob die Zelle 0 ist
        Cells(i, 2) = sgSum             'schreibt sie Summe in Spalte B neben die Null
        sgSum = 0                       'setzt die Summe zurück
    End If                              'Prüfung Ende
Next i                                  'Schleifen-Return

End Sub                                 'setzt r und sgSum zurück. Die nächste Summe wird bei der nächsten 0 berechnet.

Hier wird Spalte A abgefragt und das Ergebnis nach B geschrieben. Das anzupassen, bekommt man auch ohne echte VBA-Kenntnisse hin -> Kommentare lesen

Schöne Grüße

 

Für eine Version, die Dir die Gesamtsumme in Spalte C schreibt, ergänzt Du den Code oben bei der Variablen-Deklaration mit (beachte das führende Komma)

Nach schreibt die Summe in Spalte B dann noch diese zwei Zeilen:

Grüße

 

...dann sollst Du auch eine PQ Solution bekommen...

 

Besten Dank für deine ANtwort und Hilde. Leider hab ich von VBA bisher wneig Ahnung. Ich hab deine Code eingebene, jetzt kommt aber "Laufzeitfehler 12 - Typen unverträglich".

 

Super, vielen Dank. Das funktioniert genau so wie ich es brauche. Du hast mir sehr geholfen. Danke :)

 

Moin Hannes,
danke für die Rückmeldung. Der Laufzeitfehler tritt dann auf, wenn Du meinen Code 1:1 in Deine Tabelle übernommen hast. Denn Deine Wochentage sind natürlich keine berechenbaren Werte An Deine Spalten angepasst läuft er. Nur so als Ermutigung, doch mal was mit VBA zu probieren.

Schön, dass es die gewünschte PQ-Lösung gegeben hat.

Schöne Grüße

 

Hallo R J,

bisher glaubte ich, das List.Accumulate() das locker schafft, was List.Generate() schon lange kann, doch bei der Umstellung Deiner PQ-Funktion stelle sich heraus:
- das bei der Verwendung von Record als Ausgangsbasis (2. Parameter)
- zum Schluß die letzte Summierung als Listenelement fehlt
- und zu Beginn die '0' von Summe vom Start-Record als Listenelement drin ist

Deshalb sieht m eine Funktion jetzt so aus:

Code:

(Gewichte as list) =>
let
    ListAccu = List.Accumulate(
       Gewichte ,
       [Summe = 0, erg = {}],
       (st, cur)=>
          [
               Summe = if cur > 0 then st[Summe] + cur else 0,
               erg = st[erg] & {st[Summe]}
          ])
in
    List.RemoveFirstN(ListAccu[erg] & {ListAccu[Summe]}, 1)

Gruß von Luschi
aus klein-Paris

 

Hallo Luschi,
ich hab deine Lösung jetzt ausgiebig getestet und sie funktioniert bei mir auch.
Allerdings habe ich doch ein Problem:

Wenn Kollegen von mir die Daten aktualisieren wollen, geht das nicht. Es taucht folgender Fehler auf: [Expression Error] Während der Auswertung wurde ein zyklischer Verweis erkannt.

Komischerweise kommt der Fehler bei mir nicht. Allerdings ist in PowerQuery, die Spalte mit den kummulierten Gewichten rot markiert und es zeigt mir an das dort ein Fehler vorliegt. Allerdings gibt es keine Information welche Art von Fehler. Die Aktualisierung geht bei mir trotz dieses Fehlers, bei Kollegen aber eben nicht.

Gibts hierzu Ideen? Wäre sehr dankbar.

 

Moin,

das sagt MS zur Fehlermeldung. Ok, etwas seltsam formuliert, aber im Grunde geht es um einen Zirkelbezug.
Das hängt vermutlich mit Deinem Aufruf der Funktion zusammen. Wir kennen ja nicht Deinen ganzen Quellcode.
Ich weiß auch nicht wie Luschi oder Du die Funktion benannt hast, ob sie intern oder extern ist.

Ich hab Luschis Funktion mal als externe (also eigenständige) Funktion in das Projekt eingefügt und sie fktSummenViaAccumulate genannt.
Dann sollte folgender M-Code (mit der im Projekt eingefügten, eigenständigen Funktion fktSummenViaAccumulate von Luschi) funktionieren.
Der Aufruf erfolgt im 2. Abfrageschritt bei Gewichte = fktSummenViaAccumulate(Quelle[#"Gewicht/kg"]),

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Gewichte = fktSummenViaAccumulate(Quelle[#"Gewicht/kg"]),
    #"Hinzugefügter Index" = Table.AddIndexColumn(Quelle, "Index", 0, 1, Int64.Type),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Hinzugefügter Index", "kum. Gewicht", each Gewichte{[Index]}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte",{"Index", "Gewicht/kg"})
in
    #"Entfernte Spalten"

 

Danke für deine Antwort. Das muss ich mal bei mir testen.
Was genau meinst du mit interne oder externe Funktion? Also am besten an meinem konkreten Beispiel.
Bis zu diesem Anwendungsfall hab ich in PQ nur mit Abfragen gearbeitet, aber noch nie mit Funktionen. Ist daher alles noch ziemlich neu für mich.

 

eine externe Funktion ist praktisch eine separate Abfrage. Halt nur als Funktion. Du erstellst quasi eine neue, leere Abfrage, ersetzt den besteheden Code durch Luschis Code, benennst sie (die Abfrage) um in fktSummenViaAccumulate und schon hast Du eine neue, externe Abfrage, bzw. Funktion.

Intern wäre sie, wenn Du die Funktion in eine bestehende Abfrage einbaust.
Im Bsp. mach ich das jetzt nicht mit List.Accumulate sondern mit List.Generate: Das Prinzip ist aber das gleiche...

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
 
    Gewichte =
        let
            GewListe = Quelle[#"Gewicht/kg"],
            Maxwert = List.Count(GewListe),
        
            ListGenerate = List.Generate
            (
                () =>   [x = 0, Summe = GewListe{0}],               //Initialisierung Startwerte
                (_) =>  _[x] < Maxwert,                             //Abbruchbedingung
                (_) =>  [                                           //auszuführende Anweisungen
                            x = _[x] +1, 
                            Summe = if GewListe{x} = 0 then 0 else _[Summe]+GewListe{x}
                        ],
                (_) =>  _[Summe]                                    //Ergebnis
            )
        in
            ListGenerate,                                           //zurückgegebene Liste
        
    AddIDX = Table.AddIndexColumn(Quelle, "Index", 0, 1, Int64.Type),
    AddKumSum = Table.AddColumn(AddIDX, "kum. Gewicht", each Gewichte{[Index]}),
    DelIDX = Table.RemoveColumns(AddKumSum,{"Index"})
in
    DelIDX

Hier wird die Liste der kumulierten Gewichte quasi über die interne (also in der Abfrage integrierten) Funktion ListGenerate (ohne Punkt), durch die M-Funktion List.Generate (mit Punkt) gebildet

Wobei ListGenerate (ohne Punkt) einfach nur ein individuell vergebener Variablenname ist, den Du auch TanteHilde nennen könntest.... wenn es das Verständnis verbessert...

 

Danke für die Erklärung. dann hab ich das ganze aber glaube ich schon als externe Abfrage mit eingebaut. Die Liste auf die ich dann zugreife wird bei mir auch im Abfragefenster separat angezeigt (in meinem angehängten Bild "Tank 1 Liste") welche durch die Funktion "Abfrage Tank 1 Liste" generiert wird. Diese Liste baue ich dann anschließend in die Abfrage "Tank 1" ein. Tank 1 ist hier das Synonym für "Gewicht/kg" in meiner Beispieldatei.
Es zeigt mir dann eben Fehler (100%) in "Tank 1 kum" an. Aber Werte werden dort trotzdem richtig angezeigt. Ich könnte mir vorstellen, dass dort das Problem liegt. Komisch fin eich aber, dass es bei mir funktioniert und bei meinen Kollegen nicht.

 

...ruf mal statt der Liste die Funktion direkt auf. So wie im Post #9 im Schritt Gewichte demonstriert:

Gewichte = fktSummenViaAccumulate(Quelle[#"Gewicht/kg"]),

Wichtig ist, dass Du immer darauf achtest, auf welchen Abfrageschritt sich die Funktion beziehen soll. Das muss nicht immer der direkte Vorgängerschritt sein. Es ist bei Deiner Konstellation durchaus möglich, dass sich Deine Tankliste auf eine andere Abfrage bezieht, die dann den Zirkelbezug verursacht, oder, Du versuchst die Tankliste aus der Abfrage zu erstellen, die die Liste erstellt (und sich somit selbst aufrufen muss, obwohl sie gerade mit sich selbst beschäftigt ist) und schwupp... schon hast Du den Zirkelbezug....
Aber ohne Deine Abfrage konkret zu kennen, lässt sich auch nichts konkretes dazu sagen. Lade doch mal die Datei hoch...

 

Ich bin heute nicht mehr dazugekommen selbst nochmal rumzuspielen und den Fehler zu finden, aber anbei nochmal eine Beispieldatei die genau den Fehler hat. Vielleicht hilft das.