Daten abfragen und verarbeiten mit Excel und Power BI
E-Book, Deutsch, 364 Seiten
ISBN: 978-3-446-47811-4
Verlag: Carl Hanser
Format: PDF
Kopierschutz: Wasserzeichen (»Systemvoraussetzungen)
- Flexible und stabile Abfragen auch bei schwierigen Datenquellen
- Professionelle und effiziente Lösungen mit der Abfragesprache M
- Nützliche Beispiele Schritt für Schritt erklärt
- Ihr exklusiver Vorteil: E-Book inside beim Kauf des gedruckten Buches
- Neu in der 2. Auflage: KI-Funktionen und eigene Excel-Datentypen aus Power Query
Copy & Paste war gestern! Wer heute Daten in ein Auswertungsmodell einliest, nutzt am besten eine automatisierte und standardisierte Lösung. Microsoft Excel und Microsoft Power BI Desktop enthalten hierfür den Power Query-Editor, mit dem Sie eine Verbindung zu fast jeder Datenquelle herstellen können.
In diesem Buch lernen Sie, dieses vielseitige 'Programm im Programm' zu nutzen, um Ihren Datenimport zu automatisieren: Nicht nur das Einlesen, sondern auch die komplexe Datenaufbereitung wird einmalig als Abfrage gespeichert und kann anschließend immer wieder ausgeführt werden. Grundlage ist die Technologie Power Query, die zuerst als Add-In für Excel 2010 entwickelt wurde und nun fester Bestandteil von Excel und Power BI ist.
Dank der benutzerfreundlichen Oberfläche des Abfrage-Editors lernen Sie schnell, Daten aus verschiedenen Quellen einzulesen und einfache Transformationen wie Filtern, Gruppieren oder Ersetzen durchzuführen. Für komplexe Transformationen greifen Sie auf die integrierte Abfragesprache zurück, die unter dem Kürzel M bekannt ist (offiziell Power Query Formula Language). Mit präzisen Erklärungen und anschaulichen Beispielen hilft Ihnen dieses Buch, das volle Potenzial von Power Query auszuschöpfen und professionelle Abfragen in M zu schreiben.
AUS DEM INHALT
- Grundlagen des Power Query-Abfrage-Editors
- Abfragen auf Dateien, Datenbanken und Online-Quellen
- Mehrere Datenquellen kombinieren
- Komplexe Transformationen mit M
- Über 400 M-Funktionen anschaulich erklärt
- M-Funktionen selbst erstellen
- Fehlerbehandlung und -vermeidung
- VBA-Makros und M-Abfragen kombinieren
- Abfragen effizienter und schneller machen
Autoren/Hrsg.
Fachgebiete
- Mathematik | Informatik EDV | Informatik Programmierung | Softwareentwicklung Programmier- und Skriptsprachen
- Mathematik | Informatik EDV | Informatik Business Application Tabellenkalkulation
- Mathematik | Informatik EDV | Informatik Daten / Datenbanken
- Mathematik | Informatik EDV | Informatik Programmierung | Softwareentwicklung Microsoft Programmierung
- Wirtschaftswissenschaften Betriebswirtschaft Wirtschaftsmathematik und -statistik
Weitere Infos & Material
3 Transformieren: Aufbereiten der abgerufenen Daten Das Layout der Quelldaten ist nicht immer geeignet für die gewünschten Auswertungen. Oft muss erst eine Menge verschoben, gefiltert, ersetzt und gelöscht werden, bis eine brauchbare Analyse oder Visualisierung erstellt werden kann. Zudem benötigen Sie meist nur einen bestimmten Teil aus einer größeren Tabelle oder Datenbank. Je weniger unnötige Daten Sie ansammeln, umso kleiner und schneller ist Ihre Datei. Aus diesen Gründen wird oft viel Arbeitszeit damit verbracht, Daten „in die richtige Form zu bringen“, bevor mit der eigentlichen Auswertung begonnen werden kann. Besonders frustrierend ist dies, wenn die gleichen Aktionen regelmäßig wiederholt werden müssen, z. B. wenn die gleiche Auswertung monatlich oder wöchentlich erstellt werden muss. Der Power-Query-Abfrage-Editor kann Ihnen in solchen Fällen sehr viel Zeit sparen. Sie müssen die Daten nicht jedes Mal selbst aufbereiten, sondern nur einmal die nötigen Arbeitsschritte aufzeichnen. Die aufgezeichneten Transformationsschritte werden dann bei jeder Aktualisierung der Abfrage innerhalb weniger Sekunden ausgeführt. 3.1 Der Power Query-Editor Um beim Erstellen einer Abfrage zum Abfrage-Editor zu gelangen, klicken Sie im Navigator-Fenster nicht auf wie im letzten Beispiel, sondern stattdessen auf oder . Das folgende Beispiel verdeutlicht, wie der Editor funktioniert. Öffnen Sie Excel oder Power BI in einer neuen Datei und erstellen Sie eine Abfrage auf die Datei 3-01-Umsatz_Kwik-E-Mart.csv. Da es sich um eine CSV-Datei handelt, wählen Sie den entsprechenden Dateityp aus. Es erscheint ein Fenster, in dem Sie die Text-Codierung und das Trennzeichen auswählen können. In diesem Fall wählen Sie UTF-8 und das Trennzeichen Komma. Der Text wird so in mehrere Spalten aufgeteilt. In der Vorschau sehen Sie direkt, ob Sie richtig gewählt haben. Bei CSV-Dateien im deutschen Sprachraum wird üblicherweise das Komma als Dezimaltrennzeichen und das Semikolon als Markierung der Spaltengrenzen verwendet. Bei der Beispieldatei handelt es sich um eine CSV-Datei, wie sie international üblich ist: Das Komma trennt die Spalten und der Punkt markiert die Dezimalstellen bei Zahlen. Solche Dateien können in Excel bei deutscher Regionseinstellung nicht richtig angezeigt werden – was in internationalen Unternehmen schon viele Nerven gekostet hat. Eine Abfrage schafft hier Abhilfe. Klicken Sie unten im Fenster auf die Schaltfläche bzw. (je nach Version). Daraufhin öffnet sich der Power Query-Editor. Obwohl er auf den ersten Blick ähnlich wie ein Excel-Fenster aussieht, ist sein Zweck ein ganz anderer. Sie können beispielsweise nicht den Wert einzelner Zellen verändern. Auch Formatierungen oder Rahmen sind hier nicht möglich. Bild 3.1 Der Power Query-Editor in Excel. Der Power Query-Editor sieht in Excel fast genauso aus wie in Power BI Desktop. In diesem Fenster werden Transformationsschritte festgelegt, die auf die Quelldaten angewendet werden. Bei jeder Aktualisierung der Abfrage werden die Schritte wieder nacheinander abgearbeitet. Auf diese Weise können Sie auch aufwendige Aufgaben so automatisieren, dass sie künftig mit einem einzigen Klick erledigt werden. Die Schritte werden am rechten Fensterrand unter Angewendete Schritte aufgelistet. Hier sind auch schon zwei Schritte angelegt: 1. Quelle: Das ist der erste Schritt bei fast jeder Abfrage. Er stellt die Verbindung zu den Quelldaten, also in diesem Beispiel der CSV-Datei, her. 2. Geänderter Typ: Dieser Schritt wird automatisch eingefügt, ist aber nicht immer sinnvoll. Power Query versucht hier, die Datentypen der einzelnen Spalten zu erkennen. In diesem Beispiel brauchen Sie diesen Schritt nicht, da wir die Datentypen später selbst festlegen werden. Sie können ihn also gleich entfernen. 3.2 Transformationsschritte löschen Wenn Sie den letzten Schritt („Geänderter Typ“) anklicken, sehen Sie links daneben ein Kreuz. Klicken Sie darauf, um den Schritt zu entfernen. Im Abfrage-Editor gibt es keine Rückgängig-Funktion wie in den meisten anderen Programmen. Doch jede Aktion, die Sie vornehmen, wird in einem Transformierungsschritt aufgezeichnet. Wenn Sie einen Fehler machen, können Sie also einfach den entsprechenden Schritt löschen. Bild 3.2 Mithilfe des Kreuz-Symbols werden Schritte gelöscht. Beachten Sie jedoch, dass jeder Schritt auch die nachfolgenden Schritte beeinflusst. Sollten bereits Arbeitsschritte nach dem entfernten Schritt existieren, kann es sein, dass diese nicht mehr wie gewünscht funktionieren oder Fehlermeldungen erzeugen. Nur beim Entfernen des letzten Schritts müssen Sie sich darüber keine Sorgen machen. 3.3 Zeilen filtern Wenn Sie sich die Vorschau-Tabelle ansehen, stellen Sie fest, dass sie eigentlich in mehrere Tabellen aufgeteilt ist. Oberhalb der Tabellenüberschriften stehen immer sieben Kopfzeilen, die zum Teil leer sind und zum Teil Zusatzinformationen enthalten, die für die Auswertung nicht relevant sind (z. B. „Department“). Diese Kopfzeilen sollten entfernt werden, damit sich am Ende nur eine zusammenhängende Tabelle ergibt. Die überflüssigen Zeilen haben die Gemeinsamkeit, dass sie ab Spalte drei leer sind. Nutzen Sie daher die Filterfunktion, indem Sie auf das Quadrat mit dem Filterpfeil rechts neben dem Spaltentitel Column3 klicken. Bild 3.3 Mit der Filter-Funktion reduzieren Sie die Tabelle auf das Wesentliche. In der Filter-Box sehen Sie eine Auswahl aus den verschiedenen Einträgen in der Spalte. Nur die angekreuzten Einträge verbleiben nach dem Filtern in der Tabelle. Standardmäßig werden bis zu 1000 verschiedene Einträge angezeigt. Sollte es mehr als 1000 geben, er scheint eine Schaltfläche . Mit dieser Schaltfläche können dann alle Einträge angezeigt werden (was jedoch je nach Tabellengröße eine Weile dauert). Zusätzlich zu den Einträgen, die per Häkchen ausgewählt werden können, gibt es je nach Typ der Spalte noch spezielle Filter. Mit den Textfiltern können Sie beispielsweise festlegen, dass alle Einträge herausgefiltert werden, die eine bestimmte Zeichenfolge enthalten. Analog gibt es Zahlenfilter (z. B. alle Zahlen größer als 5) oder Datumsfilter (z. B. nur dieses Jahr), falls die Spalte den entsprechenden Datentyp hat. Klicken Sie auf . Alternativ können Sie auch das Häkchen neben dem Eintrag (leer) wegklicken und auf klicken. 3.4 Überschriften (Header) einfügen In den meisten Fällen werden Sie Überschriften für die Spalten Ihrer Tabellen benötigen. Bisher haben wir nur die generischen Standard-Überschriften Column1, Column2 usw. Die eigentlichen Überschriften liegen in der ersten Zeile. Klicken Sie im Register im Bereich Transformieren auf die Schaltfläche . Die erste Zeile ist nun in den Überschriften-Bereich gerutscht. Jedoch hatte anfangs jede der einzelnen Tabellen eine Überschriften-Zeile. Die Zeilen mit Zwischenüberschriften können nun auch entfernt werden. Nutzen Sie hierzu wieder die Filterfunktion in der dritten Spalte, entfernen Sie das Häkchen neben dem Wort Refill und klicken Sie auf . Die Tabelle sollte nun nur noch die reinen Umsatzdaten enthalten. Sie können Spaltenüberschriften übrigens auch direkt ändern: Führen Sie einen Doppelklick auf die Spaltenüberschrift der letzten Spalte (Revenue) aus. Ersetzen Sie den Spaltentitel durch den neuen Titel Umsatz und bestätigen Sie mit der -Taste. 3.5 Spalten entfernen Sie haben gesehen, wie Sie Zeilen über die Filterfunktion entfernen. Das Entfernen von Spalten geht noch einfacher: Markieren Sie die Spalte Refill, indem Sie auf den Spaltentitel...
