Inhalte
Bei fast jeder Power Query-Schulung, wenn es darum geht, wie erstellte Abfragen aktualisiert werden und die Leute sehen, wie neue Daten beim Aktualisieren alte Daten ersetzen, fragt mich einer der Zuhörer: „Ist es möglich sicherzustellen, dass beim Aktualisieren die alten Daten vorhanden sind? wurden auch irgendwo gespeichert und die gesamte Update-Historie war sichtbar?
Die Idee ist nicht neu und die Standardantwort darauf wird „nein“ sein – Power Query ist standardmäßig so konfiguriert, dass alte Daten durch neue ersetzt werden (was in den allermeisten Fällen erforderlich ist). Wenn Sie es jedoch wirklich wollen, können Sie diese Einschränkung umgehen. Und die Methode ist, wie Sie später sehen werden, sehr einfach.
Betrachten Sie das folgende Beispiel.
Nehmen wir an, wir haben eine Datei vom Client als Eingabedaten (nennen wir es, sagen wir, Quelle) mit einer Liste von Produkten, die er kaufen möchte, in Form einer „intelligenten“ dynamischen Tabelle mit dem Namen Anwendung:
In einer anderen Datei (nennen wir es analog Empfänger) erstellen wir eine einfache Abfrage, um eine Tabelle mit Produkten aus der Quelle über zu importieren Daten – Daten abrufen – Aus Datei – Aus Excel-Arbeitsmappe (Daten – Daten abrufen – Aus Datei – Aus Excel-Arbeitsmappe) und laden Sie die resultierende Tabelle in das Blatt hoch:
Wenn der Kunde in Zukunft beschließt, Änderungen an der Bestellung in seiner Datei vorzunehmen Quelle, dann nach Aktualisierung unserer Anfrage (per Rechtsklick oder via Daten – Alle aktualisieren) sehen wir die neuen Daten in der Datei Empfänger – alles serienmäßig.
Stellen wir nun sicher, dass bei der Aktualisierung nicht die alten Daten durch neue ersetzt werden, sondern die neuen an die alten angehängt werden – und zwar unter Hinzufügung einer Datums-Uhrzeit, damit ersichtlich ist, wann diese konkreten Änderungen waren gemacht.
Schritt 1. Hinzufügen von Datum und Uhrzeit zur ursprünglichen Abfrage
Lassen Sie uns eine Anfrage öffnen AnwendungImportieren unserer Daten aus Quelle, und fügen Sie eine Spalte mit Datum und Uhrzeit der Aktualisierung hinzu. Dazu können Sie die Schaltfläche verwenden Benutzerdefinierte Spalte Tab Spalte hinzufügen (Spalte hinzufügen – Benutzerdefinierte Spalte), und geben Sie dann die Funktion ein DateTime.LocalNow – Analogon der Funktion Die TDATA (JETZT) â Microsoft Excel:
Nach dem Klicken auf OK Sie sollten am Ende eine hübsche Spalte wie diese haben (vergessen Sie nicht, das Datum-Uhrzeit-Format dafür mit dem Symbol in der Spaltenüberschrift einzustellen):
Wenn Sie möchten, können Sie für die Platte, die in das Blatt für diese Spalte hochgeladen wurde, das Datum-Uhrzeit-Format mit Sekunden für eine größere Genauigkeit festlegen (Sie müssen dem Standardformat einen Doppelpunkt und „ss“ hinzufügen):
Schritt 2: Abfrage nach alten Daten
Lassen Sie uns nun eine weitere Abfrage erstellen, die als Puffer fungiert, der die alten Daten vor der Aktualisierung speichert. Auswählen einer beliebigen Zelle der resultierenden Tabelle in der Datei Empfänger, wählen Sie auf der Registerkarte aus Datum Befehl Aus Tabelle/Bereich (Daten — Aus Tabelle/Bereich) or Mit Blättern (Vom Blatt):
Wir tun nichts mit der in Power Query geladenen Tabelle, wir nennen die Abfrage beispielsweise alte Daten und drücken Sie Startseite – Schließen und laden – Schließen und laden nach… – Nur Verbindung herstellen (Home — Schließen&Laden — Schließen&Laden nach… — Nur Verbindung herstellen).
Schritt 3. Verbinden alter und neuer Daten
Nun zurück zu unserer ursprünglichen Anfrage Anwendung und fügen Sie von unten die alten Daten aus der vorherigen Pufferanforderung mit dem Befehl hinzu Startseite — Anfragen hinzufügen (Home — Abfragen anhängen):
Das ist alles!
Es bleibt, zu Excel zurückzukehren Startseite — Schließen und herunterladen (Startseite — Schließen&Laden) und versuchen Sie ein paar Mal, unsere gesamte Struktur mit der Schaltfläche zu aktualisieren Alle aktualisieren Tab Datum (Daten — Alle aktualisieren). Bei jedem Update ersetzen die neuen Daten nicht die alten Daten, sondern schieben sie nach unten, wobei der gesamte Update-Verlauf erhalten bleibt:
Ein ähnlicher Trick kann beim Import aus beliebigen externen Quellen (Internetseiten, Datenbanken, externe Dateien usw.) angewendet werden, um die alten Werte für die Historie bei Bedarf beizubehalten.
- Pivot-Tabelle über mehrere Datenbereiche hinweg
- Zusammenstellen von Tabellen aus verschiedenen Dateien mit Power Query
- Sammeln von Daten aus allen Blättern des Buches in einer Tabelle