Datensets mit Power Query zusammenstellen

Häufig werden Sie nicht über die Zugriffsrechte auf die Datenbanken verfügen, um Ihre benötigten Daten zusammenzustellen. Was tun Sie in diesem Fall? Sie verwenden Power Query, es ist direkt in Excel integriert. Sie importieren die Daten, die Sie erhalten, erstellen verschiedene Power Query-Abfragen, führen Power Query-Abfragen zusammen und modellieren die Daten so, wie Sie sie brauchen. In diesem Beispiel geht es darum, Daten aus zwei Excel-Arbeitsmappen in dieser leeren Excel-Datei hier zusammenzuführen, und zwar eine Datei mit Daten aus einer Gehaltsstudie und eine Datei mit wiederverwendbaren Postleitzahlen aus den USA. Letztere stammen aus der Website geonames.org. Wählen Sie also Daten > Daten abrufen > Aus Datei > Aus Excel-Arbeitsmappe, wechseln Sie in den gewünschten Ordner, das ist hier der Übungsordner 06_06, klicken Sie auf die Postleitzahlen-Datei und wählen Sie Importieren. Klicken Sie auf die Tabelle Postleitzahlen, um die Daten anzuzeigen. Und sofort fällt auf, dass die Postleitzahlen nur dreistellig sind. Das kann eigentlich gar nicht sein. Klicken Sie daher auf "Daten transformieren", um das Problem zu untersuchen. Die erste Spalte heißt TextPostleitzahl und man sieht an den Zahlen hier, dass es sich um ein Zahlenformat handelt, d.h., Power Query hat hier automatisch den Datentyp geändert und es wird auch bestätigt durch den letzten Schritt hier "Geänderter Typ". Löschen Sie diesen Schritt. Und dann werden die Postleitzahlen korrekt angezeigt mit allen führenden Nullen. Weisen Sie der Spalte noch explizit das Datenformat Text zu. Die zweite Spalte Postleitzahl brauchen Sie nicht mehr. Klicken Sie mit der rechten Maustaste in die Beschriftung und wählen Sie den Befehl Entfernen. Dann klicken Sie auf "Schließen & laden" und die Postleitzahlen werden direkt in die Arbeitsmappe geladen. Die führenden Nullen der Postleitzahlen und des Textformats sind erhalten geblieben. Die nächsten Daten, die wir brauchen, sind die Daten der Gehaltsstudie. Wählen Sie Daten > Daten abrufen > Aus Datei > Aus Excel-Arbeitsmappe und klicken Sie auf Gehaltsstudie und dann auf Importieren. Wählen Sie die Tabelle Studie, um die Daten anzuzeigen und klicken Sie dann auf "Daten transformieren". Es werden 343 Umfragedatensätze angezeigt. Prüfen Sie hier zunächst das Format der Postleitzahlen. Stellen Sie diese Spalte an den Anfang der Tabelle. Klicken Sie mit der rechten Maustaste in die Beschriftung und wählen Sie dann "An den Anfang". So sind die Postleitzahlen griffbereit für das Verbinden der Daten. Das Zahlenformat muss aber für das Zusammenführen dasselbe Format aufweisen wie in der anderen Abfrage, nämlich Text. Sie haben jetzt also zwei Abfragen, einmal die Abfrage Studie und dann einmal die Abfrage Postleitzahlen. Diese zwei Datensätze können Sie jetzt zusammenführen. Wählen Sie auf der Registerkarte Start in der Gruppe Kombinieren den kleinen Pfeil der Schaltfläche "Abfragen zusammenführen" und wählen Sie hier den Befehl "Abfragen als neue Abfrage zusammenführen". So erstellen Sie eine neue Abfrage. Die erste Tabelle ist hier schon richtig angegeben, die Tabelle Studie. Wählen Sie im zweiten Drop-down-Listenfeld die Tabelle Postleitzahlen. Die Join-Art ist ein "Linker äußerer Join", das passt gut, nämlich alle Datensätze aus der ersten Tabelle und alle übereinstimmenden Datensätze aus der zweiten Tabelle. Markieren Sie noch die Postleitzahlen-Spalten. Und dann wird hier schon angezeigt, dass die Auswahl mit 337 von 334 Zeilen der ersten Tabelle übereinstimmt. Vermutlich ist irgendeine Postleitzahl falsch oder vielleicht fehlt auch eine Postleitzahl. Klicken Sie auf OK. Die zusammengeführte Abfrage wird erstellt. Hier Links wird ihr Name angezeigt, klicken Sie mit der rechten Maustaste in den Namen und wählen Sie Umbenennen und vergeben Sie den Namen StudiePLZ. Hier werden jetzt alle Umfragedaten angezeigt plus ganz rechts eine Spalte für die zusammengeführte Tabelle Postleitzahlen. Klicken Sie hier auf die Schaltfläche mit den zwei Pfeilen, um die Tabelle zu erweitern. Sie können dann wählen, welche Daten aus dieser zusammengeführten Tabelle angezeigt werden sollen. Entscheiden Sie sich hier z.B. für die TextPostleitzahl und die Zeitzone. Bestätigen Sie mit OK. Jetzt werde hier zwei Spalten angezeigt, einmal die TextPostleitzahl und einmal die Zeitzone. Und beiden Spalten ist der Tabellenname Postleitzahlen vorangestellt. Wenn Sie das nicht möchten, dann klicken Sie im entsprechenden Schritt "Erweiterte Postleitzahlen" auf das Zahnrad und löschen hier unten den Spaltennamenpräfix. Bestätigen Sie mit OK. Und jetzt steht hier nur noch TextPostleitzahl und Zeitzone. Erstellen Sie nun eine weitere zusammengeführte Abfrage, die nur die Umfrageergebnisse aus dem Bundesstaat Alabama mit seinen 67 Counties enthalten soll. Wählen Sie auf der Registerkarte Start in der Gruppe Kombinieren den Pfeil des ersten Befehls und wählen Sie dann "Abfragen als neue Abfrage zusammenführen". Sie verwenden dieselben Tabellen, oben also die Tabelle Studie, dann die Tabelle Postleitzahlen. Es geht wieder um die beiden Postleitzahl-Spalten. Auch hier stimmt der linke äußere Join, obwohl es hier kein links und rechts, sondern ein Oben und ein Unten gibt. Und hier steht wieder die Übereinstimmung und bestätigen Sie diese Einstellung mit OK. Benennen Sie diese Abfrage um, und zwar in AlabamaErgebnis. Auch hier werden zuerst wieder alle Studiendaten angezeigt und dann ganz rechts die Daten der zusammengeführten Tabelle Postleitzahlen, die Sie auch hier erweitern können. Und hier möchten Sie z.B. Stadt, Staat, Region, Breitengrad und Längengrad anzeigen lassen. Und der Spaltenname soll nicht als Präfix verwendet werden. Jetzt werden hier die Spalten der Tabelle angezeigt. Und es fehlt noch ein Schritt, nämlich Sie müssen nach dem Bundesstaat Alabama filtern. Klicken Sie dazu auf den Pfeil in der Spalte Staat, deaktivieren Sie "Alles auswählen" und aktivieren Sie das Kontrollkästchen für Alabama. Bestätigen Sie mit OK. Dann klicken Sie auf die Schaltfläche "Schließen & laden". Rechts werden die verschiedenen Abfragen angezeigt. AlabamaErgebnis und StudiePLZ, das sind die zusammengeführten Abfragen. Und wenn Sie hier auf eine Abfrage klicken, dann wechselt Excel automatisch in das entsprechende Tabellenblatt. Speichern Sie das Ergebnis bei Bedarf unter einem anderen Namen. Die Ergebnisdatei finden Sie aber ebenfalls im Ordner 06_06. Das Zusammenführen und Modellieren von Daten geht heute so viel einfacher als früher. Und wenn Sie die benötigten Daten zusammengeführt haben, können Sie sie bereinigen und als Basis für Ihre Berichte einsetzen.