Power Query E-Book

Gil Raviv

• Herausgeber: dpunkt.verlag
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Deutsch
• Veröffentlichungsjahr: 2020

Seitenzahl: 717

Beschreibung

Schluss mit dem Datenchaos!

• Lernen Sie, wie Sie Daten aus verschiedenen Quellen zusammenführen, kombinieren, transformieren und analysieren.
• mit über 200 Übungsaufgaben
• einfache Schritt-für-Schritt-Anleitungen für den schnellen Erfolg
• inkl. Übungsdateien zum kostenlosen Download

Mit diesem praktischen Buch, geschrieben von Microsoft-Experte Gil Raviv, lernen Sie, wie Sie mit Power Query in Power BI und Excel:
- Daten für Analysen einfach vorbereiten (importieren, bereinigen, aufarbeiten, umarbeiten)
- Daten aus verschiedenen Quellen (z. B. csv- und xls-Dateien, Access-, SAP- oder SQL-Datenbanken oder Statistiken aus dem Internet) kombinieren und anpassen
- Datensätze zusammenführen und vergleichen
- Aufgaben automatisieren
- u. v. m.

Leseprobe

Gil Raviv, Microsoft Data Platform MVP und Power BI-Blogger unter https://DataChant.com. Als Senior Program Manager für Microsoft Excel leitete er das Design und die Integration von Power Query als Datenabruf-Data-Wrangling-Technologie der nächsten Generation von Excel 2016 und wurde zum ausgewiesenen Experten von M, der Formelsprache von Power Query. Raviv verfügt über 20 Jahre Erfahrung im Bereich Programmierung und ist Inhaber von vier US-Patenten in den Bereichen soziale Netzwerke, Cybersicherheit und Datenanalyse. Er hat Datenanalysen und Big Data genutzt, um Sicherheitsprodukte, angefangen bei fortgeschrittener Bedrohungserkennung bis hin zum Kinderschutz auf Facebook, bereitzustellen.

In seinem Blog auf DataChant.com befasst sich Gil Raviv mit Power BI und Power Query, seit er Anfang 2016 in sein neues Zuhause nach Chicago gezogen ist. Als Group Manager Analytics Practice bei Avanade begleitet er Fortune-500-Kunden bei der Entwicklung moderner Self-Service-Analytics-Lösungen auf Basis von Power BI und Azure. Sie erreichen Gil Raviv über [email protected].

Zu diesem Buch – sowie zu vielen weiteren dpunkt.büchern – können Sie auch das entsprechende E-Book im PDF-Format herunterladen. Werden Sie dazu einfach Mitglied bei dpunkt.plus+:

www.dpunkt.plus

Power Query

In Excel und Power BI Daten sammeln, kombinieren und transformieren

Gil Raviv

Gil Raviv

Übersetzung: Rainer G. Haselier

Lektorat: Sandra Bollenbacher

Copy-Editing: Petra Heubach-Erdmann, Düsseldorf

Satz: Gerhard Alfes, mediaService, Siegen, www.mediaservice.tv

Herstellung: Stefanie Weidner

Umschlaggestaltung: Helmut Kraus, www.exclam.de

ISBN:

Print

978-3-86490-727-2

PDF

978-3-96088-926-7

ePub

978-3-96088-927-4

mobi

978-3-96088-928-1

1. Auflage 2020

Translation Copyright für die deutschsprachige Ausgabe © 2020 dpunkt.verlag GmbH

Wieblinger Weg 17

69123 Heidelberg

Authorized translation from the English language edition, entitled COLLECT, COMBINE, AND TRANSFORM DATA USING POWER QUERY IN EXCEL AND POWER BI, 1st Edition by GIL RAVIV, ISBN 978-1509307951, published by Pearson Education, Inc, publishing as Microsoft Press, Copyright © 2019 by Gil Raviv

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by any information storage retrieval system, without permission from Pearson Education, Inc.

ISBN of the English language edition: 9781509307951

German language edition published by DPUNKT.VERLAG GMBH, Copyright © 2020

Hinweis:

Dieses Buch wurde auf PEFC-zertifiziertem Papier aus nachhaltiger Waldwirtschaft gedruckt. Der Umwelt zuliebe verzichten wir zusätzlich auf die Einschweißfolie.

Schreiben Sie uns:

Falls Sie Anregungen, Wünsche und Kommentare haben, lassen Sie es uns wissen: [email protected]

Die vorliegende Publikation ist urheberrechtlich geschützt. Alle Rechte vorbehalten. Die Verwendung der Texte und Abbildungen, auch auszugsweise, ist ohne die schriftliche Zustimmung des Verlags urheberrechtswidrig und daher strafbar. Dies gilt insbesondere für die Vervielfältigung, Übersetzung oder die Verwendung in elektronischen Systemen.

Es wird darauf hingewiesen, dass die im Buch verwendeten Soft- und Hardware-Bezeichnungen sowie Markennamen und Produktbezeichnungen der jeweiligen Firmen im Allgemeinen warenzeichen-, marken- oder patentrechtlichem Schutz unterliegen.

Alle Angaben und Programme in diesem Buch wurden mit größter Sorgfalt kontrolliert. Weder Autor noch Verlag noch Übersetzer können jedoch für Schäden haftbar gemacht werden, die in Zusammenhang mit der Verwendung dieses Buches stehen.

5 4 3 2 1 0

Inhaltsverzeichnis

Vorwort

Einleitung

Für wen ist dieses Buch?

Annahmen über Sie, den Leser

Wie ist dieses Buch aufgebaut?

Über die Begleitdateien

Systemvoraussetzungen

Hinweiskästen

Support und Feedback

Errata, Updates und Support

Wir wollen von Ihnen hören

Bleiben Sie am Ball

Kapitel 1

Einführung in Power Query

Was ist Power Query?

Ein kurzer geschichtlicher Überblick zu Power Query

Wo finde ich Power Query?

Hauptkomponenten von Power Query

Daten abrufen und Konnektoren

Die Hauptbereiche des Power Query-Editors

Übung 1-1: Ein erster Blick auf Power Query

Zusammenfassung

Kapitel 2

Grundlegende Herausforderungen bei der Datenvorbereitung

Die Bedeutung aus codierten Spalten extrahieren

Die AdventureWorks-Herausforderung

Übung 2-1: Der alte Weg: Excel-Formeln verwenden

Übung 2-2, Teil 1: Der neue Weg

Übung 2-2, Teil 2: Nachschlagetabellen zusammenführen

Übung 2-2, Teil 3: Fakten- und Nachschlagetabellen

Das Feature Spalte aus Beispielen verwenden

Übung 2-3, Teil 1: Einführung in Spalte aus Beispielen

Praktische Verwendung von Spalte aus Beispielen

Übung 2-3, Teil 2: Größe in Buckets/Klassen konvertieren

Informationen aus Textspalten extrahieren

Übung 2-4: Hyperlinks aus Nachrichten extrahieren

Datumswerte bearbeiten

Übung 2-5: Verarbeitung unterschiedlicher Datumsformate

Übung 2-6: Datumswerte aus verschiedenen Gebietsschemata

Datum- und Uhrzeitelemente extrahieren

Das Modell vorbereiten

Übung 2-7: Aufteilen von Daten in Nachschlagetabellen und Faktentabellen

Wenn eine Beziehung fehlschlägt

Übung 2-8: Durch Trennzeichen getrennte Werte in Zeilen aufteilen

Zusammenfassung

Kapitel 3

Daten aus mehreren Quellen kombinieren

Ein paar Tabellen anfügen

Zwei Tabellen anfügen

Übung 3-1: Beispiel Fahrräder und Zubehör

Übung 3-2, Teil 1: Abfragen als neu anfügen verwenden

Übung 3-2, Teil 2: Abfrageabhängigkeiten und Verweise

Drei oder mehr Tabellen anfügen

Übung 3-2, Teil 3: Fahrräder + Zubehör + Komponenten

Übung 3-2, Teil 4: Fahrräder + Zubehör + Komponenten + Kleidung

Tabellen in größerem Maßstab anhängen

Tabellen aus einem Ordner anfügen

Übung 3-3: AdventureWorks-Produkte aus einem Ordner anfügen

Ein paar Gedanken zum Importieren aus Ordnern

Tabellenblätter aus einer Arbeitsmappe anfügen

Übung 3-4: Tabellenblätter anfügen – Die Lösung

Ein robuster Ansatz zum Kombinieren mehrerer Tabellenblätter

Zusammenfassung

Kapitel 4

Kombinieren von Tabellen mit unterschiedlichen Datenstrukturen

Das Problem von Tabellen mit unterschiedlichen Datenstrukturen

Was sind nicht übereinstimmende Tabellen?

Symptome und Risiken von nicht übereinstimmenden Tabellen

Übung 4-1: Nicht übereinstimmende Spaltennamen auflösen – Der reaktive Ansatz

Nicht übereinstimmende Tabellen aus einem Ordner kombinieren

Übung 4-2, Teil 1: Das Symptom der fehlenden Werte demonstrieren

Übung 4-2, Teil 2: Die Annahme der gleichen Reihenfolge und die Vereinheitlichung der Überschriften als Lösung

Übung 4-3: Einfache Normalisierung mit Table.TransformColumnNames

Die Konvertierungstabelle

Übung 4-4: Transponieren und die Verwendung einer Konvertierungstabelle

Übung 4-5: Entpivotieren, Zusammenführen und erneut Pivotieren

Übung 4-6: Nur die Spaltennamen transponieren

Übung 4-7: M zum Normalisieren von Spaltennamen verwenden

Zusammenfassung

Kapitel 5

Den Kontext erhalten

Den Kontext der Dateinamen und Arbeitsblätter beibehalten

Übung 5-1, Teil 1: Die Technik der benutzerdefinierte Spalte

Übung 5-1, Teil 2: Umgang mit Kontextinformationen in Dateinamen und Tabellenblattnamen

Titel vor dem Anfügen beibehalten

Übung 5-2: Die Titel mithilfe von Drilldown erhalten

Übung 5-3: Titel beim Import aus einem Ordner erhalten

Titel nach dem Anfügen beibehalten

Übung 5-4: Aus mehreren Tabellenblättern in derselben Arbeitsmappe die Titel beibehalten

Kontexthinweise verwenden

Übung 5-5: Verwenden einer Indexspalte als Hinweis

Übung 5-6: Identifizieren des Kontexts anhand der Nachbarschaft zu einer Zelle

Zusammenfassung

Kapitel 6

Tabellen entpivotieren

Schlecht gestaltete Tabellen erkennen

Entpivotieren – Eine Einführung

Übung 6-1: »Spalten entpivotieren« und »Andere Spalten entpivotieren« verwenden

Übung 6-2: Nur ausgewählte Spalten entpivotieren

Umgang mit Gesamtergebnissen

Übung 6-3: Gesamtergebnisse entpivotieren

2×2 Hierarchieebenen entpivotieren

Übung 6-4: 2×2 Hierarchieebenen mit Datumswerten entpivotieren

Übung 6-5: 2×2 Hierarchieebenen entpivotieren

Umgang mit Teilergebnissen in entpivotierten Daten

Übung 6-6: Umgang mit Teilergebnissen

Zusammenfassung

Kapitel 7

Fortgeschrittenes Entpivotieren und Pivotieren von Tabellen

Tabellen mit mehreren Hierarchieebenen entpivotieren

Die virtuelle PivotTable, Zeilenfelder und Spaltenfelder

Übung 7-1: In den AdventureWorks-Daten N×M Hierarchieebenen entpivotieren

Die Sequenz für das Entpivotieren verallgemeinern

Übung 7-2: Am Ende beginnen

Übung 7-3: FnUnpivotSummarizedTable erstellen

Die Transformation Spalte pivotieren

Übung 7-4: Eine falsch entpivotierte Tabelle wiederherstellen

Übung 7-5: Tabellen mit mehrzeiligen Datensätzen pivotieren

Zusammenfassung

Kapitel 8

Herausforderungen bei der Zusammenarbeit lösen

Lokale Dateien, Parameter und Vorlagen

Zugriff auf lokale Dateien – auf die verkehrte Weise

Übung 8-1: Verwenden eines Parameters für einen Pfadnamen

Übung 8-2: Erstellen einer Vorlage in Power BI

Übung 8-3: Parameter in Excel verwenden

Übung 8-3, Teil 2: Die Datenkombination neu erstellen

Mit freigegebenen Dateien und Ordnern arbeiten

Daten aus Dateien auf OneDrive for Business oder SharePoint importieren

Übung 8-4: Migrieren Ihrer Abfragen, um eine Verbindung mit OneDrive for Business oder SharePoint herzustellen

Übung 8-5: Von lokalen Ordnern zu SharePoint-Ordnern migrieren

Sicherheitsaspekte

Alle Abfragen mithilfe des Dokumentinspektors in Excel entfernen

Zusammenfassung

Kapitel 9

Einführung in die Power Query-Formelsprache M

M lernen

Die Lernstufen

Stufe 1: Nur Benutzeroberfläche

Stufe 2: Einfache Formeländerungen in der Bearbeitungsleiste

Stufe 3: M in benutzerdefinierten Spalten

Stufe 4: Benutzerdefinierte Funktionen und der erweiterte Editor

Stufe 5: Fortgeschrittene Schleifen (List.Accumulate und List.Generate)

Stufe 6: Freie Codeerstellung im Erweiterten Editor

Online-Ressourcen

Offline-Ressourcen

Übung 9-1: Mit #shared die integrierten Funktionen erkunden

Die Bausteine von M

Übung 9-2: Hello World

Der let-Ausdruck

Zugriffsbereiche beim Zusammenführen von Ausdrücken aus mehreren Abfragen

Typen, Operatoren und integrierte Funktionen in M

Grundlegende M-Typen

Der Datentyp Zahl (Number)

Der Datentyp Uhrzeit (Time)

Der Datentyp Datum (Date)

Der Datentyp Dauer (Duration)

Der Datentyp Text

Der Datentyp null

Der Datentyp logisch

Komplexe M-Typen

Der Datentyp Liste (List)

Der Typ Datensatz (Record)

Der Typ Tabelle (Table)

Bedingungen und if-Ausdrücke

Benutzerdefinierte Funktionen

Der Ausdruck each

Fortgeschrittene Themen

Fehlerbehandlung

Faule und eifrige Auswertung

Schleifen

Rekursion

List.Generate

List.Accumulate

Zusammenfassung

Kapitel 10

Von Stolperfallen zu robusten Abfragen

Ursachen und Auswirkungen der Stolperfallen

Bewusstsein

Bewährte Verfahren

Modifikationen der M-Ausdrücke

Stolperfalle 1: Die Bearbeitungsleiste ignorieren

Übung 10-1: Verwenden der Bearbeitungsleiste, um statische Verweise auf Spaltennamen zu entdecken

Stolperfalle 2: Geänderte Typen

Stolperfalle 3: Gefährliches Filtern

Übung 10-2, Teil 1: Schwarze Produkte filtern

Die Logik hinter der Filterbedingung

Übung 10-2, Teil 2: Werte im Filterbereich suchen

Stolperfalle 4: Spalten neu anordnen

Übung 10-3, Teil 1: Eine Teilmenge der Spalten neu anordnen

Übung 10-3, Teil 2: Die benutzerdefinierte Funktion FnReorderSubsetOfColumns

Stolperfalle 5: Spalten entfernen und auswählen

Übung 10-4: Umgang mit den Zufallsspalten in der Wide World Importers-Tabelle

Fehlende Spalte ignorieren

Spalten basierend auf ihrer Position auswählen oder entfernen

Spalten basierend auf ihren Namen auswählen oder entfernen

Stolperfalle 6: Spalten umbenennen

Übung 10-5: Die Zufallsspalten in der Wide World Importers-Tabelle umbenennen

Stolperfalle 7: Eine Spalte in Spalten aufteilen

Übung 10-6: Eine falsche Aufteilung durchführen

Stolperfalle 8: Spalten zusammenführen

Weitere Stolperfallen und Techniken für robuste Abfragen

Zusammenfassung

Kapitel 11

Einfache Textanalyse

In Textspalten nach Schlüsselwörtern suchen

Übung 11-1: Einfache Erkennung von Schlüsselwörtern

Schlüsselwörter mithilfe eines kartesischen Produkts erkennen

Übung 11-2: Ein kartesisches Produkt implementieren

Übung 11-3: Erkennen von Schlüsselwörtern mithilfe einer benutzerdefinierten Funktion

Welche Methode sollte verwendet werden: statische Suche, kartesisches Produkt oder benutzerdefinierte Funktion?

Eine Textspalte in einzelne Wörter aufteilen

Übung 11-4: Einfaches Aufteilen in einzelne Wörter

Übung 11-5: Stoppwörter herausfiltern

Übung 11-6: Mithilfe von »nach Wörtern teilen« nach Schlüsselwörtern suchen

Übung 11-7: Wortwolken in Power BI erstellen

Zusammenfassung

Kapitel 12

Fortgeschrittene Textanalyse: Bedeutung extrahieren

Microsoft Azure Cognitive Services

API-Schlüssel und Ressourcenbereitstellung in Azure

Vor- und Nachteile der Verwendung der Cognitive Services in Power Query

Textübersetzung

Referenz der Textübersetzungs-API

Übung 12-1: Einfache Übersetzung

Übung 12-2: Mehrere Nachrichten übersetzen

Stimmungsanalyse

Wie lautet der API-Aufruf für die Stimmungsanalyse?

Übung 12-3: Implementieren von FnGetSentiment, einer benutzerdefinierten Funktion für die Stimmungsanalyse

Übung 12-4: Stimmungsanalyse für große Datasets ausführen

Schlüsselwörter extrahieren

Übung 12-5: Die Logik der Stimmungsanalyse zum Extrahieren von Schlüsselwörtern verwenden

Unterstützung für mehrere Sprachen

Übung 12-6: Die Logik der Stimmungsanalyse zur Erkennung der Sprache verwenden

Zusammenfassung

Kapitel 13

Abschlussprojekt: Alles miteinander kombinieren

Übung 13-1: Retten Sie Wide World Importers

Tipps

Teil 1: Start der Lösung

Teil 2: Die Funktion für das Entpivotieren aufrufen

Teil 3: Die Pivotieren-Sequenz für die Umsätze 2018

Teil 4: Die Umsätze 2018 und 2015 bis 2017 kombinieren

Übung 13-2: Tabellen vergleichen und den Hacker aufspüren

Tipps

Übung 13-2: Die Lösung

Die verborgene Nachricht des Hackers in der kompromittierten Tabelle entdecken

Zusammenfassung

Abbildungsverzeichnis

Index

Vorwort

Als wir uns daran machten, das ursprüngliche Power Query-Add-In für Excel zu erstellen, hatten wir eine einfache, aber ehrgeizige Mission: Verbindungen zu den Daten der Welt herzustellen und sie zu transformieren. Fünf Jahre später haben wir das anfängliche Excel-Add-In hinter uns gelassen und PowerQuery ist direkt in Excel, Power BI, PowerApps und eine wachsende Reihe von Produkten, die Daten extrahieren und transformieren müssen, integriert. Jedoch bleibt unsere ursprüngliche Mission weitestgehend unverändert. Mit der ständig zunehmenden Heterogenität der Daten scheint unsere Mission in vielerlei Hinsicht noch ehrgeiziger und herausfordernder zu sein als jemals zuvor. Ein Großteil der heutigen Computerlandschaft ist auf Daten ausgerichtet, aber die Daten sind nicht immer da, wo oder wie Sie sie benötigen – wir werden Power Query mit dem Ziel weiterentwickeln, diese Lücke zwischen den rohen und den gewünschten Datenzuständen zu überbrücken.

Während der gesamten Entwicklung und Weiterentwicklung von Power Query hat die Anwender-Community durch Vorschläge und Feedback eine entscheidende Rolle bei der Gestaltung des Produkts gespielt. Die Community hat auch eine zentrale Rolle bei der Entwicklung wertvoller Lerninhalte gespielt. Als einer der treibenden Kräfte der nativen Integration von Power Query in Excel 2016 ist Gil gut aufgestellt, um wertvolle Einblicke und Tipps für eine Vielzahl von Szenarien zu geben. Auch nach seiner Zeit bei Microsoft ist Gil ein aktives und einflussreiches Mitglied der Power Query-Community geblieben. Viel Spaß beim Erstellen von Abfragen!

– Sid Jayadevan, Engineering Manager für Power Query, Microsoft Corporation

Für Leser, die mit Power Query nicht vertraut sind, ist es eine unglaublich leistungsstarke und erweiterbare Engine, die das Herzstück der Microsoft BI-Tools darstellt. Es verbessert Self-Service Business Intelligence (BI) mit einer intuitiven und konsistenten Benutzeroberfläche zum Erforschen, Kombinieren und Verfeinern von Daten über eine Vielzahl von Quellen hinweg. Da die Datenvorbereitung in der Regel ungefähr 80% jeder BI-Lösung ausmacht, ist ein gutes Verständnis von Power Query der erste Schritt bei jeder Art von Berichtserstellung und Datenanalyse. Neben den zentralen Power Query-Features behandelt Gil auch fortgeschrittene Themen, wie zum Beispiel die Verwendung von Power Query zur Automatisierung der Datenvorbereitung und -bereinigung, die Verbindung zu sozialen Netzwerken, um zu erfassen, was Ihre Kunden über Ihr Unternehmen sagen, und wie Sie Power Query für Dienste aus dem Bereich maschinelles Lernen nutzen, um Sentiment-Analysen durchzuführen, und wie man die Sprache M verwendet, um praktisch jede Art von Rohdaten zu einer Quelle von Erkenntnissen zu machen, aus denen Sie Nutzen ziehen können. Dieses Buch zeichnet sich dadurch aus, dass es zusätzliche Begleitinhalte mit fertigen Beispielen, Datenquellen und Schritt-für-Schritt-Anleitungen bietet.

Gil ist ein ehemaliges Mitglied des Excel-Teams und des Microsoft Data-Teams. Er hat einen direkten Beitrag zu den Features und dem Design von Power Query geliefert, verfügt über eine erstaunliche Fülle an Wissen zu Power Query und zeigt, wie mit Power Query schwierige Probleme bei der Datenintegration vereinfacht werden können. Trotz des inhärent erweiterbaren und benutzerfreundlichen Designs von Power Query kann es dennoch schwierig sein, es für Unternehmensszenarien einzusetzen. Glücklicherweise ist Gil Raviv als engagiertes Community-Mitglied, Verfasser von Forenbeiträgen, Konferenzmoderator, Peer-Mentor und Power BI MVP ein Meister darin, komplexe Konzepte in sehr einfach zu folgenden Schritte zu zerlegen, um so den Leser erfolgreich werden zu lassen. Daher ist dieses Buch ein Muss für jeden BI-Spezialisten, Datenanalytiker oder Geschäftsmann, der aus den Daten, mit denen er zu tun hat, Mehrwert erzielen möchte.

– Charles Sterling, Senior Program Manager, Microsoft Corporation

Einleitung

Wussten Sie, dass in Microsoft Excel, Power BI und anderen Produkten eine Datentransformationstechnologie integriert ist, mit der Sie an Ihren Daten wahre Wunder vollbringen, sich wiederholende manuelle Arbeit vermeiden und bis zu 80% Ihrer Zeit einsparen können?

Jedes Mal, wenn Sie ähnliche Daten in Ihre Arbeitsmappe kopieren/einfügen und manuell bereinigen, verschwenden Sie wertvolle Zeit, möglicherweise ohne sich der Alternativen bewusst zu sein, mit denen Sie dies besser und schneller erledigen können.

Jedes Mal, wenn Sie sich darauf verlassen, dass andere Ihre Daten in die richtige Form und den richtigen Zustand bringen, sollten Sie wissen, dass es eine einfachere Möglichkeit gibt: Gestalten Sie Ihre Daten einmal um und freuen Sie sich darüber, dass das in Zukunft automatisiert funktioniert.

Jedes Mal, wenn Sie schnelle fundierte Entscheidungen treffen müssen, Sie sich aber massiven Herausforderungen bei der Datenbereinigung stellen müssen, wissen Sie, dass Sie diese Herausforderungen jetzt leicht bewältigen können und ein nie da gewesenes Potenzial erhalten, um die für das Erhalten von Einblicken benötigte Zeit zu verkürzen.

Sind Sie bereit für Veränderung? Sie sind dabei, die wahnsinnige Frustration der sich wiederholenden manuellen Datenbereinigung durch schiere Aufregung und Spaß zu ersetzen, und durch diesen Prozess können Sie sogar Ihre Datenqualität verbessern und neue Erkenntnisse gewinnen.

Excel, Power BI, Analysis Services und PowerApps nutzen eine bahnbrechende Technologie zum Abrufen und Transformieren von Daten, Power Query, die es jeder Person mit grundlegenden Excel-Kenntnissen ermöglicht, den Datenimport, die Umgestaltung von Daten und die Datenbereinigung zu automatisieren. Mit ein paar einfachen Klicks in der Benutzeroberfläche und einer einheitlichen Benutzererfahrung für eine Vielzahl von Datenquellen und Formaten können Sie jede Herausforderung bei der Datenvorbereitung lösen und zum Meister im Data-Wrangling werden.

In diesem Buch werden Sie echte Datenherausforderungen angehen und lernen, wie Sie diese mit Power Query lösen können. Im Rahmen von mehr als 70 Herausforderungen und 200 Übungsdateien im Begleitmaterial zu diesem Buch importieren Sie chaotische und unzusammenhängende Tabellen und arbeiten sich durch die Erstellung automatisierter und gut strukturierter Datensätze, die für die Analyse bereitstehen. Die meisten Techniken sind einfach zu befolgen und können leicht in Ihrem eigenen Arbeitsalltag wiederverwendet werden.

Für wen ist dieses Buch?

Ganz gleich, ob Sie für sich wiederholende Datenvorbereitungsaufgaben in Excel zuständig sind oder Power BI-Berichte für Ihr Unternehmen entwickeln, dieses Buch ist genau für Sie. Analysten, Business Intelligence-Spezialisten und ETL-Entwickler können ihre Produktivität durch die Techniken in diesem Buch steigern. Da die Power Query-Technologie zum primären Datenstapel in Excel geworden ist und der Einsatz von Power BI enorm zugenommen hat, wird dieses Buch Ihnen helfen, den Weg in Ihrem Unternehmen zu ebnen und eine größere Wirkung zu erzielen.

Das Buch wurde geschrieben, damit alle Power Query-Benutzer neue Fähigkeiten erlernen können. Gleichgültig, ob Sie ein neuer, ein fortgeschrittener oder ein Benutzer mit moderaten Kenntnissen sind, Sie werden nützliche Techniken finden, die Ihnen helfen, die nächste Stufe zu erklimmen.

Annahmen über Sie, den Leser

Grundkenntnisse in Excel oder Power BI werden vorausgesetzt. Während jeder Excel-Benutzer von diesem Buch profitieren kann, würden Sie viel mehr davon profitieren, wenn Sie eines der folgenden Kriterien erfüllen. (Beachten Sie, dass die Erfüllung eines einzelnen Kriteriums ausreichend ist.)

Sie kopieren und fügen regelmäßig Daten aus den gleichen Quellen in Excel ein und müssen diese Daten häufig bereinigen.

Sie erstellen Berichte in Excel oder Power BI, die mit externen Datenquellen verbunden sind, und möchten diese verbessern.

Sie sind mit PivotTables in Excel vertraut.

Sie sind mit Power Pivot in Excel vertraut und möchten Ihre Datenmodelle vereinfachen.

Sie sind mit Power Query vertraut und möchten auf die nächste Stufe wechseln.

Sie entwickeln Geschäftsanwendungen mit PowerApps und müssen sich mit Datenquellen verbinden, deren Datensätzen wenig aufgeräumt sind.

Sie sind Entwickler von Analysis Services und möchten Ihre ETL-Entwicklung beschleunigen.

Wie ist dieses Buch aufgebaut?

Das Buch ist in 13 Kapitel unterteilt, die bei allgemeinen und einfacheren Datenherausforderungen beginnen und zu fortgeschrittenen und spezifischen Szenarien übergehen, die es zu meistern gilt. Es ist vollgepackt mit praktischen Übungen und Schritt-für-Schritt-Lösungen, die die notwendigen Techniken für die Bewältigung realer Herausforderungen bei der Datenvorbereitung bereitstellen und als langfristige Lernressource dienen, unabhängig davon, wie viele neue Funktionen in Zukunft in Power Query veröffentlicht werden.

In Kapitel 1, »Einführung in Power Query«, wird Ihnen Power Query vorgestellt und Sie erhalten die grundlegenden Kenntnisse, um die nachfolgenden Übungen durchführen zu können.

In Kapitel 2, »Grundlegende Herausforderungen bei der Datenvorbereitung«, erfahren Sie, wie Sie relativ einfache typische Datenherausforderungen bewältigen. Wenn Sie im Rahmen Ihres Arbeitsalltags häufig Datenbereinigungsaufgaben durchführen, werden Sie dieses Kapitel sehr hilfreich finden. Sie lernen die einfachsten Techniken zur Automatisierung Ihrer Datenreinigungsaufgaben kennen, die Sie mit wenigen Mausklicks und ohne Programmierkenntnisse durchführen können. Wenn Power Query noch neu für Sie ist, sparen Sie bereits Zeit, wenn Sie die Techniken in diesem Kapitel befolgen.

In Kapitel 3, »Daten aus mehreren Quellen kombinieren«, erfahren Sie, wie Sie getrennte Datasets kombinieren und mehrere Tabellen im Power Query-Editor anfügen. Sie erfahren, wie Sie mehrere Arbeitsmappen aus einem Ordner anfügen und mehrere Arbeitsblätter auf robuste Weise kombinieren: Wenn später neue Arbeitsblätter hinzugefügt werden, genügt eine einzelne Aktualisierung des Berichts, um die neuen Daten an Ihren Bericht anzuhängen.

In Kapitel 4, »Kombinieren von nicht übereinstimmenden Tabellen«, erklimmen Sie die nächste Ebene und lernen, wie Sie nicht übereinstimmende Tabellen, also Tabellen, die zwar die gleichen Daten enthalten, jedoch unterschiedlich aufgebaut sind, kombinieren. In realen Szenarien sind Ihre Daten segmentiert und isoliert und das Format und die Struktur der Daten sind oft nicht konsistent. Wenn Sie lernen, wie Sie nicht übereinstimmende Tabellen normalisieren, erhalten Sie neue Einblicke in strategische Geschäftsszenarien.

In Kapitel 5, »Den Kontext erhalten«, erfahren Sie, wie Sie externen Kontext in Ihren Tabellen extrahieren und beibehalten und Titel sowie andere Metainformationen, wie Dateinamen und Arbeitsblattnamen, kombinieren, um die angehängten Tabellen mit diesen Informationen zu bereichern.

In Kapitel 6, »Tabellen entpivotieren«, erfahren Sie, wie Sie die Tabellenstruktur verbessern können, um eine bessere Darstellung der Entitäten zu erhalten, die die Daten darstellen. Sie erfahren, dass die Transformation Entpivotieren ein Eckpfeiler für die Bereinigung der Adressierung schlecht gestalteter Tabellen ist, und nutzen die Leistungsfähigkeit von Entpivotieren, um Ihre Tabellen für eine bessere Analyse neu zu strukturieren. Außerdem erfahren Sie, wie Sie mit verschachtelten Tabellen umgehen und warum und wie Sie Summen und Zwischensummen in Ihren Quelldaten ignorieren.

In Kapitel 7, »Fortgeschrittenes Entpivotieren und Pivotieren von Tabellen«, setzen Sie Ihre Reise zur Transformation Entpivotieren fort und verallgemeinern eine Lösung, die Ihnen hilft, beliebige zusammengefasste Tabelle zu entpivotieren, unabhängig davon, wie viele Hierarchieebenen auf Zeilen- und Spaltenebene vorliegen. Anschließend erfahren Sie, wie Sie Pivotieren anwenden, um mehrzeilige Datensätze zu verarbeiten. Die in diesem Kapitel gezeigten Techniken ermöglichen es Ihnen, eine Vielzahl von Transformationen durchzuführen und überstrukturierte Datasets in eine leistungsstarke und agile Analyseplattform umzuformen.

Als Berichtsautor geben Sie Ihre Berichte häufig an andere Autoren in Ihrem Team oder Unternehmen weiter. In Kapitel 8, »Herausforderungen bei der Zusammenarbeit lösen«, erfahren Sie mehr über grundlegende Herausforderungen bei der Zusammenarbeit und wie Sie diese mithilfe von Parametern und Vorlagen lösen können.

In Kapitel 9, »Einführung in die Power Query-Formelsprache M«, werden Sie tiefer in die Abfragesprache M eintauchen, die verwendet werden kann, um Ihre Abfragen anzupassen, um mehr zu erreichen und Ihre Transformation in einem größeren Umfang von Herausforderungen wiederzuverwenden. In diesem Kapitel lernen Sie die wichtigsten Bausteine von M kennen – Syntax, Operatoren, Typen und eine Vielzahl integrierter Funktionen. Wenn Sie kein fortgeschrittener Benutzer sind, können Sie dieses Kapitel überspringen und es zu einem späteren Zeitpunkt lesen. M vollständig zu meistern ist keine Voraussetzung, um ein Experte für das Data-Wrangling zu werden, jedoch kann die Fähigkeit, die M-Formeln bei Bedarf zu ändern, Ihre Möglichkeiten erheblich steigern.

Die Benutzererfahrung mit dem Power Query-Editor in Excel und Power BI ist sehr erträglich, da sie Ihre alltäglichen, aber entscheidenden Datenvorbereitungsaufgaben in einen automatisierten Aktualisierungsfluss verwandeln kann. Leider treten auf dem Weg zum Beherrschen des Data-Wrangling typische Fehler auf, die Sie möglicherweise im Power Query-Editor machen, was zur Erstellung anfälliger Abfragen führt, die nicht aktualisiert werden können oder zu falschen Ergebnissen führen, wenn sich die Daten ändern. In Kapitel 10, »Von Stolperfallen zu robusten Abfragen«, lernen Sie die häufigsten Fehler oder Fallstricke kennen und wie Sie diese durch robuste Abfragen vermeiden können, deren Aktualisierung nicht fehlschlägt und die nicht zu falschen Ergebnissen führen.

In Kapitel 11, »Grundlegende Textanalyse«, nutzen Sie Power Query, um grundlegende Einblicke in Textfeeds zu gewinnen. Viele Tabellen in Ihren Berichten enthalten möglicherweise bereits reichlich Textspalten, die bei der Analyse häufig ignoriert werden. Sie lernen, wie Sie allgemeine Transformationen anwenden, um Bedeutung aus Wörtern zu extrahieren, wie Sie Schlüsselwörter erkennen und häufig auftretende Wörter, die in der Regel keine Relevanz für den Dokumentinhalt besitzen, sogenannte Stoppwörter, ignorieren. Außerdem werden Sie lernen, wie Sie das kartesische Produkt für komplexe Textsuchen anwenden.

In Kapitel 12, »Fortgeschrittene Textanalyse: Bedeutung extrahieren«, werden Sie von grundlegenden zu erweiterten Textanalysen übergehen und lernen, wie Sie Sprachübersetzungen, Stimmungsanalysen und Schlüsselworterkennung mithilfe von Microsoft Cognitive Services anwenden. Mit dem Power Query-Webconnector und einigen grundlegenden M-Funktionen können Sie ohne die Hilfe von Datenwissenschaftlern oder Softwareentwicklern die wirkliche Bedeutung aus Text extrahieren und die Leistungsfähigkeit künstlicher Intelligenz nutzen.

Schließlich werden Sie in Kapitel 13, »Das finale Projekt: Alles miteinander kombinieren«, vor die letzte Herausforderung des Buches treten und Ihr ganzes Wissen auf die Probe stellen, indem Sie Ihre neuen erworbenen Data-Wrangling-Kenntnisse auf eine große Herausforderung anwenden. Wenden Sie die Techniken aus diesem Buch an, um Dutzende von Arbeitsblättern aus mehreren Arbeitsmappen zu kombinieren, die Daten zu entpivotieren und zu pivotieren und um Wide World Importers vor einem groß angelegten Cyberangriff zu retten!

Über die Begleitdateien

Die Begleitdateien zum Buch können Sie unter https://www.dpunkt.de/powerquery.html kostenlos herunterladen.

In den Begleitdateien finden Sie:

Excel-Arbeitsmappen und CSV-Dateien, die als chaotische und schlecht formatierte Datenquellen für alle Übungen im Buch verwendet werden. Es ist daher nicht nötig, eine externe Datenbank zu installieren, um die Übungen durcharbeiten zu können.

Excel-Arbeitsmappen und Power BI-Berichte mit den Lösungen, die die erforderlichen Abfragen enthalten, um die einzelnen Datenherausforderungen zu lösen.

In der folgenden Tabelle sind die Übungsdateien aufgeführt, die zum Ausführen der Übungen in diesem Buch erforderlich sind.

Kapitel

Dateien

Kapitel 1: Einführung in Power Query

K01U01.xlsx

K01U01 - Lösung.xlsx

K01U01 - Lösung.pbix

Kapitel 2: Grundlegende Herausforderungen bei der Datenvorbereitung

K02U01.xlsx

K02U01 - Lösung.xlsx

K02U02.xlsx

K02U02 - Lösung - Teil 1.xlsx

K02U02 - Lösung - Teil 2.xlsx

K02U02 - Lösung - Teil 3.xlsx

K02U02 - Lösung - Teil 1.pbix

K02U02 - Lösung - Teil 2.pbix

K02U02 - Lösung - Teil 3.pbix

K02U03 - Lösung.xlsx

K02U03 - Lösung - Teil 2.xlsx

K02U03 - Lösung.pbix

K02U03 - Lösung - Teil 2.pbix

K02U04.xlsx

K02U04 - Lösung.xlsx

K02U04 - Lösung.pbix

K02U05.xlsx

K02U05 - Lösung.xlsx

K02U05 - Lösung.pbix

K02U06.xlsx

K02U06 - Lösung.xlsx

K02U06 - Lösung.pbix

K02U07.xlsx

K02U07 - Lösung.xlsx

K02U07 - Lösung.pbix

K02U08.xlsx

K02U08 - Lösung.xlsx

K02U08 - Lösung.pbix

Kapitel 3: Daten aus mehreren Quellen kombinieren

K03U01 - Fahrräder.xlsx

K03U01 - Kleidung.xlsx

K03U01 - Komponenten.xlsx

K03U01 - Zubehör.xlsx

K03U03 - Produkte.zip

K03U03 - Lösung.xlsx

K03U03 - Lösung.pbix

K03U04 - Jahr pro Arbeitblatt.xlsx

K03U04 - Lösung 01.xlsx

K03U04 - Lösung 02.xlsx

K03U04 - Lösung 01.pbix

K03U04 - Lösung 02.pbix

Kapitel 4: Kombinieren von nicht übereinstimmenden Tabellen

K04U01 - Fahrräder.xlsx

K04U01 - Zubehör.xlsx

K04U02 - Produkte.zip

K04U02 - Lösung.xlsx

K04U02 - Lösung.pbix

K04U03 - Produkte.zip

K04U03 - Lösung.xlsx

K04U03 - Lösung.pbix

K04U04 - Produkte.zip

K04U04 - Konvertierungstabelle.xlsx

K04U04 - Lösung - Vertauschen.xlsx

K04U04 - Lösung - Vertauschen.pbix

K04U05 - Lösung - Entpivotieren.xlsx

K04U05 - Lösung - Entpivotieren.pbix

K04U06 - Lösung - Überschriften transponieren.xlsx

K04U06 - Lösung - Überschriften transponieren.pbix

K04U07 - Lösung - M.xlsx

K04U07 - Lösung - M.pbix

Kapitel 5: Den Kontext erhalten

K05U01 - Fahrräder.xlsx

K05U01 - Fahrräder & Zubehör.xlsx

K05U01 - Zubehör.xlsx

K05U01 - Lösung.xlsx

K05U01 - Lösung 2.xlsx

K05U01 - Lösung.pbix

K05U01 - Lösung 2.pbix

K05U02 - Fahrräder.xlsx

K05U02 - Lösung.xlsx

K05U02 - Lösung.pbix

K05U03 - Produkte.zip

K05U03 - Lösung.xlsx

K05U03 - Lösung.pbix

K05U04 - Produkte.xlsx

K05U04 - Lösung.xlsx

K05U04 - Lösung.pbix

K05U05 - Produkte.xlsx

K05U05 - Lösung.xlsx

K05U05 - Lösung.pbix

K05U06 - Produkte.xlsx

K05U06 - Schnellstart.xlsx

K05U06 - Schnellstart.pbix

K05U06 - Lösung.xlsx

K05U06 - Lösung.pbix

Kapitel 6: Tabellen entpivotieren

K06U01.xlsx

K06U02.xlsx

K06U03.xlsx

K06U03 - Falsche Lösung.pbix

K06U03 - Lösung.xlsx

K06U03 - Lösung.pbix

K06U04.xlsx

K06U04 - Lösung.xlsx

K06U04 - Lösung.pbix

K06U05.xlsx

K06U05 - Lösung.xlsx

K06U05 - Lösung.pbix

K06U06.xlsx

K06U06 - Lösung.xlsx

K06U06 - Lösung.pbix

Kapitel 7: Fortgeschrittenes Entpivotieren und Pivotieren von Tabellen

K07U01.xlsx

K07U01 - Lösung.xlsx

K07U01 - Lösung.pbix

K07U02.xlsx

K07U02.pbix

K07U03 - Lösung.xlsx

K07U03 - Lösung.pbix

K07U04.xlsx

K07U04 - Lösung.xlsx

K07U04 - Lösung.pbix

K07U05 - Lösung.xlsx

K07U05 - Lösung.pbix

Kapitel 8: Herausforderungen bei der Zusammenarbeit lösen

K08U01.xlsx

K08U01 - Alice.xlsx

K08U01 - Alice.pbix

K08U01 - Lösung.xlsx

K08U01 - Lösung.pbix

K08U02 - Lösung.pbit

K08U02 - Lösung 2.pbit

K08U03 - Lösung.xlsx

K08U03 - Lösung 2.xlsx

K08U04 - Lösung.xlsx

K08U04 - Lösung.pbix

K08U05.xlsx

K08U05.pbix

K08U05 - Ordner.zip

K08U05 - Lösung.xlsx

K08U05 - Lösung.pbix

Kapitel 9: Einführung in die Power Query-Formelsprache M

K09U01 – Lösung.xlsx

K09U01 – Lösung.pbix

Kapitel 10: Von Stolperfallen zu robusten Abfragen

K10U01.xlsx

K10U01 - Lösung.xlsx

K10U02 - Lösung.xlsx

K10U02 - Lösung.pbix

K10U03 - Lösung.xlsx

K10U03 - Lösung.pbix

K10U04 - Lösung.xlsx

K10U04 - Lösung.pbix

K10U05.xlsx

K10U05 - Lösung.xlsx

K10U05 - Lösung.pbix

K10U06.xlsx

K10U06 - Lösung.xlsx

K10U06 - Lösung.pbix

K10U06-v2.xlsx

Kapitel 11: Grundlegende Textanalyse

Schlüsselwörter.txt

Stoppwörter.txt

K11U01.xlsx

K11U01 - Lösung.xlsx

K11U01 - Lösung.pbix

K11U02 - Lösung.xlsx

K11U02 - Aktualisierung Vergleich.xlsx

K11U02 - Lösung.pbix

K11U03 - Lösung.xlsx

K11U04 - Lösung.xlsx

K11U04 - Lösung.pbix

K11U05 - Lösung.xlsx

K11U05 - Lösung.pbix

K11U06 - Lösung.xlsx

K11U06 - Lösung.pbix

K11U07 - Lösung.pbix

Kapitel 12: Fortgeschrittene Textanalyse: Bedeutung extrahieren

K12U01 - Lösung.xlsx

K12U01 - Lösung.pbix

K12U02.xlsx

K12U02 - Lösung.xlsx

K12U02 - Lösung.pbix

K12U02 - Lösung.pbit

K12U03 - Lösung.xlsx

K12U03 - Lösung.pbix

K12U04.xlsx

K12U04.pbix

K12U04 - Lösung.xlsx

K12U04 - Lösung.pbix

K12U05 - Lösung.pbix

K12U06 - Lösung.xlsx

K12U06 - Lösung.pbix

Kapitel 13: Das finale Projekt: Alles miteinander kombinieren

K13U01 - Ziel.xlsx

K13U01.zip

K13U01 - Lösung.xlsx

K13U01 - Lösung.pbix

K13U02 - Kompromittiert.xlsx

K13U02 - Lösung.xlsx

K13U02 - Lösung.pbix

Systemvoraussetzungen

Sie benötigen die folgende Software, um die Codebeispiele für dieses Buch zu erstellen und auszuführen:

Betriebssystem: Windows 10, Windows 8, Windows 7, Windows Server 2008 R2 oder Windows Server 2012

Software: Office 365, Excel 2016 oder eine neue Version von Excel, Power BI Desktop, Excel 2013 mit Power Query Add-In oder Excel 2010 mit Power Query Add-In

Hinweiskästen

Hinweis

Tipp

Zusätzliche Informationen

Support und Feedback

Die folgenden Abschnitte enthalten Informationen zu Errata, Buchsupport, Feedback und Kontaktinformationen.

Errata, Updates und Support

Wir haben uns sehr um die Richtigkeit der in diesem Buch enthaltenen Informationen bemüht. Fehler, die seit der Veröffentlichung der englischen Originalausgabe des Buches bekannt geworden sind, werden auf der Microsoft Press-Website aufgelistet:

https://www.microsoftpressstore.com/powerquery

Sollten Sie einen Fehler finden, der noch nicht aufgeführt ist, würden wir uns freuen, wenn Sie uns auf dieser Seite darüber informieren (in englischer Sprache).

Falls Sie zusätzlichen Support benötigen, können Sie sich an den englischsprachigen Buchsupport von Microsoft Press wenden. Sie erreichen ihn unter dieser E-Mail-Adresse:

[email protected]

Mit Anmerkungen, Fragen oder Verbesserungsvorschlägen zu diesem Buch können Sie sich aber auch an den dpunkt.verlag wenden:

[email protected]

Bitte beachten Sie, dass über diese E-Mail-Adressen kein Software- oder Hardware-Support angeboten wird. Für Supportinformationen bezüglich der Software- und Hardwareprodukte von Microsoft besuchen Sie bitte die Microsoft-Website:

http://support.microsoft.com

Wir wollen von Ihnen hören

Bei Microsoft Press steht Ihre Zufriedenheit an oberster Stelle. Daher ist Ihr Feedback für uns sehr wichtig, Lassen Sie uns auf dieser englischsprachigen Website wissen, wie Sie dieses Buch finden:

https://aka.ms/tellpress

Wir wissen, dass Sie viel zu tun haben. Darum finden Sie auf der Webseite nur wenige Fragen. Ihre Antworten gehen direkt an das Team von Microsoft Press. (Es werden keine persönlichen Informationen abgefragt.) Im Voraus vielen Dank für Ihre Unterstützung.

Über Ihr Feedback per E-Mail freut sich außerdem der dpunkt.verlag über [email protected].

Bleiben Sie am Ball

Falls Sie News, Updates und so weiter zu Microsoft Press-Büchern erhalten möchten, wir sind auf Twitter:

https://twitter.com/dpunkt_verlag

KAPITEL 1

Einführung in Power Query

Wir leben in einem Zeitalter mit permanenten Updates und ständig verfügbaren Technologien. Daher hört es sich vielleicht etwas merkwürdig an, auf Aktualisieren klicken zu müssen. Und dennoch, wenn es richtig betrieben wird und sich Menschen und Kulturen neu erschaffen und beleben, kann das der Auftakt einer Renaissance darstellen.

– Satya Nadella

In diesem Kapitel

Erhalten Sie eine Einführung in Power Query und erfahren Sie, wie alles begann

Lernen Sie die Hauptkomponenten von Power Query und dem Power Query-Editor kennen

Erkunden Sie das Tool und bereiten Sie Beispieldaten für die Analyse vor

In diesem Buch lernen Sie, wie Sie die Features von Power Query nutzen, um Ihre Herausforderungen bei der Datenanalyse zu bewältigen, und wie Sie dabei bis zu 80% der für die Datenvorbereitung benötigten Zeit einsparen. Dieses Kapitel beginnt mit einer offiziellen Einführung. Power Query verdient es. Wissen Sie, während Sie diese Zeilen lesen, gibt es wahrscheinlich eine halbe Million Anwender, die gerade jetzt, genau in diesem Moment, die Zähne zusammenbeißen, während sie in Excel sich wiederholende, aber wichtige Datenvorbereitungsaufgaben durchführen. Sie tun es jeden Tag, jede Woche oder jeden Monat.

Wenn Sie mit dem Lesen dieses Buches fertig sind, werden etwa 50 Millionen Menschen manuell Datenvorbereitungsaufgaben durchgeführt haben, ohne zu wissen, dass ein Tool, das sich in Excel versteckt, nur darauf wartet, ihnen dabei zu helfen, das Leben einfacher zu machen. Einige von ihnen haben bereits gelernt, wie man erweiterte Tools wie Python und R verwendet, um ihre Daten zu säubern; andere verlassen sich auf ihre IT-Abteilungen und warten monatelang darauf, dass ihre Anfragen bearbeitet werden; die meisten von ihnen wollen nur die Arbeit erledigen und haben sich damit abgefunden, Hunderte oder sogar Tausende von Stunden damit zu verbringen, ihre Daten für die Analyse vorzubereiten. Wenn Sie oder Ihre Freunde unter diesen 50 Millionen sind, ist es Zeit, mehr über Power Query und darüber zu erfahren, wie Sie damit Ihre Datenanalysen für immer verändern.

Gleichgültig, ob Power Query neu für Sie ist oder ob Sie ein versierter Praktiker sind, dieses Kapitel hilft Ihnen bei der Vorbereitung auf die vor Ihnen liegende Reise. Im Laufe der Reise werden Sie zu einem Meister beim Data-Wrangling, bei der Datenaufbereitung und in der Lage sein, selbstständig neue Erkenntnisse aus Ihren Daten zu gewinnen.

Was ist Power Query?

Power Query ist eine bahnbrechende Technologie für das Verbinden zu unterschiedlichsten Datenquellen und für die Transformation der Daten, die in Microsoft Excel, Power BI und anderen Microsoft-Produkten zur Verfügung steht. Es ermöglicht jeder Person eine Verbindung zu einem umfangreichen Satz externer Datenquellen und sogar lokaler Daten in einer Kalkulationstabelle herzustellen und die Daten mithilfe einer einfachen Benutzeroberfläche zu sammeln, zu kombinieren und zu transformieren. Sobald die Daten gut vorbereitet sind, können sie in Excel und Power BI in einen Bericht geladen oder als Tabelle in anderen Produkten gespeichert werden. Wenn die Daten aktualisiert werden, können Benutzer ihre Berichte aktualisieren und sich an der automatisierten Transformation ihrer Daten erfreuen.

Power Query wird seit seiner Veröffentlichung von Millionen Benutzern verwendet. Aufgrund seiner erheblichen Auswirkungen auf die Möglichkeiten für Information Worker und Datenanalysten hat Microsoft beschlossen, Power Query in weitere Produkte zu integrieren. Hierzu gehören unter anderem die folgenden:

■Microsoft SQL Server Data Tools (SSDT) für SQL Server 2017 Analysis Services und Azure Analysis Services (siehe https://docs.microsoft.com/de-de/sql/analysis-services/what-s-new-in-sql-server-analysis-services-2017)

■Common Data Service for Apps, der von Microsoft PowerApps verwendet wird (siehe https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-cds-newentity-pq)

Beim Vorbereiten der Daten mithilfe des Power Query-Editors, der zentralen Benutzeroberfläche Power Query, zeichnet das Tool eine Sequenz von Transformationsanweisungen auf, sodass Sie die Datenvorbereitungssequenz in Zukunft wiederholen können. Fortgeschrittene Excel-Benutzer vergleichen dies oft mit der Makroaufzeichnung, aber es ist einfacher als das. Wenn Sie die Transformationsschritte abgeschlossen haben, können Sie die vorbereiteten Daten in einen Bericht laden. Ab diesem Zeitpunkt werden jedes Mal, wenn Sie den Bericht aktualisieren, die gleichen Transformationsschritte ausgeführt, sodass Sie viele Stunden und Tage wiederholter manueller Datenvorbereitungsarbeiten sparen.

Power Query ist wirklich einfach zu bedienen. Es stellt eine einheitliche Benutzeroberfläche zur Verfügung – unabhängig davon, aus welcher Datenquelle Sie die Daten importieren oder über welches Format die Daten verfügen. Power Query ermöglicht es Ihnen, komplexe Datenvorbereitungsszenarien mittels einer Reihe kleiner Schritte zu erstellen, die editierbar und leicht zu verstehen sind. Bei komplexeren Einsatzszenarien können fortgeschrittene Anwender jeden Schritt in der Bearbeitungsleiste oder im erweiterten Editor ändern, um die Transformationsausdrücke anzupassen (mit der M-Abfragesprache, die in Kapitel 9, »Einführung in die Power Query-Formelsprache M«, erläutert wird). Jede Abfolge von Transformationsschritten wird als Abfrage gespeichert, die in einen Bericht geladen oder von anderen Abfragen wiederverwendet werden kann, um so eine Pipeline von Transformationsbausteinen zu erstellen.

Bevor wir die einzelnen Hauptkomponenten von Power Query untersuchen, gehen wir ein paar Jahre zurück und schauen uns an, wie alles begann. Dieser kurze geschichtliche Überblick zu Power Query wird Ihnen helfen, zu verstehen, wie lange es diese Technologie bereits gibt und wie sie sich zum heutigen Status weiterentwickelt hat.

Ein kurzer geschichtlicher Überblick zu Power Query

Power Query wurde ursprünglich 2011 als Teil von Microsoft SQL Azure Labs entwickelt. Es wurde auf dem PASS Summit im Oktober 2011 unter dem Microsoft-Codenamen »Data Explorer« angekündigt. Abbildung 1-1 zeigt die ursprüngliche Benutzeroberfläche.

Abbildung 1-1Hinter dem Produkt mit dem Microsoft-Codenamen Data Explorer verbirgt sich eine frühe Version von Power Query.

Im Februar 2013 hat Microsoft das Tool als Excel-Add-In neu gestaltet und es von SQL Azure Labs abgekoppelt. Das Tool, das jetzt den Namen Data Explorer Preview for Excel trägt, wurde positioniert, um die Self-Service-BI-Erfahrung in Excel zu verbessern, indem die Untersuchung von Daten und der Zugriff auf eine breite Palette von Datenquellen für umfassendere Erkenntnisse vereinfacht wird.

Bereits von Beginn an stellte Data Explorer als Excel-Add-In eine intuitive und konsistente Benutzeroberfläche für die Erforschung, Kombination und Verfeinerung von Daten zur Verfügung, und zwar für Daten aus einer Vielzahl von Quellen, einschließlich relationaler, strukturierter und halbstrukturierter Datenbanken, OData, Web, Hadoop, Azure Market Place und mehr. Mit Data Explorer war es auch möglich, nach öffentlichen Daten auf Quellen wie Wikipedia zu suchen (eine Funktionalität, die später entfernt wurde).

Nach der Installation der Add-Ins in Excel 2010 oder 2013 war Data Explorer Preview for Excel auf der Registerkarte Data Explorer sichtbar. Diese Registerkarte in Excel hatte das gleiche Look & Feel wie das heutige Power Query-Add-In. Der Power Query-Editor wurde zu diesem Zeitpunkt als Neue Abfrage bezeichnet, und es gab keine spezifischen Power Query-Registerkarten. Wenn Sie sich die Ankündigung von Data Explorer anschauen und sich über die ursprüngliche Benutzeroberfläche als Excel-Add-In informieren wollen, schauen Sie sich dieses Video an: https://blogs.msdn.microsoft.com/dataexplorer/2013/02/27/announcing-microsoft-data-explorer-preview-for-excel.

Abbildung 1-2 zeigt Statistiken über die zunehmende Adaption von Data Explorer und seinen Übergang von SQL Azure Labs zu Excel. Laut MSDN-Profil des Data Explorer Teams bei Microsoft (https://social.msdn.microsoft.com/Profile/Data%2bExplorer%2bTeam) startete das Team seine erste Community-Aktivitäten im Oktober 2011, als Data Explorer zum ersten Mal in SQL Azure Labs veröffentlicht wurde. Seit Februar 2013, als Data Explorer als Excel-Add-In veröffentlicht wurde, hat das Community-Engagement deutlich zugenommen; der Umstieg auf Excel hatte sich eindeutig gelohnt.

Abbildung 1-2Der Punkteverlauf des Daten-Explorer-Teams auf MSDN zeigt die zunehmende Adaption von Data Explorer, nachdem das Team von SQL Azure Labs auf Excel umgeschwenkt ist.

Wie Sie an der Trendlinie im Punkteverlauf in Abbildung 1-2 sehen können, begann die Aktivität des Data Explorer Teams im Juli 2013 an Schwung zu verlieren. Es war jedoch kein negativer Moment in der Geschichte des Data Explorers – nur eine Wiedergeburt des Tools unter einem neuen Namen. Im Juli 2013 gab Microsoft die allgemeine Verfügbarkeit des Add-Ins unter dem neuen Namen Power Query-Add-In für Excel bekannt. Zu diesem Zeitpunkt bot das Add-In die gleiche Benutzererfahrung wie die neueste Version von Power Query.

Das Power Query-Team begann, monatlich Updates für das Power Query-Add-In zu veröffentlichen. Diese Entwicklungsgeschwindigkeit führte zu rascher Innovation und ständigem Wachstum der Community. Viele Benutzer und Fans halfen, das Produkt durch direktes Feedback, Foren und Blogs zu gestalten.

Das Power Query-Add-In wird immer noch ständig aktualisiert und steht als Add-In für Excel 2010 und Excel 2013 zum Download zur Verfügung. Nach der Installation wird Power Query als neue Registerkarte in Excel angezeigt, und Sie können von dieser Registerkarte aus eine Verbindung zu neuen Datenquellen herstellen.

Im Dezember 2014 veröffentlichte Microsoft eine Vorschau von Power BI Designer (https://powerbi.microsoft.com/en-us/blog/new-power-bi-features-available-for-preview). Der Power BI Designer war ein neues Clienttool für die Erstellung von Berichten, das es Business Intelligence-Experten ermöglichte, interaktive Berichte zu erstellen und sie im Power BI-Dienst zu veröffentlichen, der sich ebenfalls noch in der Previewphase befand. Power BI Designer vereinheitlichte drei Excel-Add-Ins (Power Query, Power Pivot und Power View) und war für den Erfolg von Power BI entscheidend. Im Power BI Designer behielt Power Query alle Funktionen des Excel-Add-Ins bei. Während die meisten Benutzererfahrungen identisch waren, wurde der Begriff Power Query in Power BI Designer nicht mehr verwendet. Sieben Monate später, im Juli 2015, änderte Microsoft den Namen von Power BI Designer in Power BI Desktop und gab dessen allgemeine Verfügbarkeit bekannt (https://powerbi.microsoft.com/en-us/blog/what-s-new-in-the-power-bi-desktop-ga-update).

In dieser Phase lieferte das Power Query-Team weiterhin monatliche Updates von Power Query für Excel und Power BI Desktop aus, während es mit dem Excel-Team zusammenarbeitete, um die standardmäßige Benutzererfahrung für das Abrufen von Daten in Excel vollständig zu überarbeiten.

Während das Power Query-Add-In ursprünglich von Excel getrennt war, entschied sich Microsoft, es als systemeigene Komponente zu integrieren und das Power Query-Modul in Excel als primären Data Stack zu verwenden. Im September 2015 veröffentlichte Microsoft Excel 2016 mit der integrierter Power Query-Funktionalität; die Installation eines Add-Ins war nicht mehr erforderlich. Microsoft platzierte die Power Query-Funktionalität zunächst auf die Registerkarte Daten in die Befehlsgruppe Abrufen und transformieren, die später in Daten abrufen und transformieren umbenannt wurde.

Damit war die Power Query-Technologie zum ersten Mal für die massenhafte Verwendung verfügbar und unterstützte native Excel-Funktionen wie Rückgängig und Wiederholen, Kopieren und Einfügen von Tabellen, Makroaufzeichnung und VBA. Weitere Informationen zur Power Query-Integration in Excel 2016 finden Sie unter https://blogs.office.com/en-us/2015/09/10/integrating-power-query-technology-in-excel-2016.

Im März 2017 veröffentlichte Microsoft ein Update für Office 365, das weitere Verbesserungen am Data Stack enthielt. Die Power Query-Technologie wurde damit tatsächlich zum primären Data Stack von Excel (https://support.office.com/en-us/article/unified-get-transform-experience-ad78befd-eb1c-4ea7-a55d-79d1d67cf9b3). Das Update umfasste eine Vereinheitlichung der Erfahrungen zwischen Abfragen und Arbeitsmappenverbindungen und verbesserte die Unterstützung für ODC-Dateien. Darüber hinaus wurde der Haupteinstiegspunkt zu Power Query, das Dropdownmenü Daten abrufen, als erster Befehl auf die Registerkarte Daten in den Abschnitt Daten abrufen und transformieren verschoben.

Im April 2017 veröffentlichte Microsoft SQL Server Data Tools (SSDT) und kündigte an, dass die moderne Oberfläche für den Abruf von Daten auch für tabellarische Modelle mit dem Kompatibilitätsgrad 1400 der Analysis Services zur Verfügung steht (https://blogs.msdn.microsoft.com/ssdt/2017/04/19/announcing-the-general-availability-ga-release-of-ssdt-17-0-april-2017). Mit SSDT 17.0 können Sie Power Query verwenden, um Daten in Ihren Tabellenmodellen in SQL Server 2017 Analysis Services und Azure Analysis Services zu importieren und vorzubereiten. Wenn Sie mit Analysis Services vertraut sind, können Sie hier erfahren, wie Sie Power Query hierfür verwenden:

https://docs.microsoft.com/en-us/sql/analysis-services/Tutorial-tabular-1400/as-lesson-2-get-data?view=sql-analysis-services-2017.

Obwohl sich dieses Buch auf Excel und Power BI Desktop konzentriert, sind die meisten Kapitel und Übungen des Buches auch für die Arbeit mit Analysis Services relevant, insbesondere in frühen Phasen Ihrer Projekte, in denen Sie mit chaotischen Datasets umgehen müssen.

Im März 2018 kündigte Microsoft Common Data Service (CDS) for Apps (https://powerapps.microsoft.com/en-us/blog/cds-for-apps-march) an und hat Power Query zusammen mit Microsoft Flow als eines der wichtigsten Datenimporttools integriert (siehe Abbildung 1-3). Microsoft hat Power Query über seinen ursprünglichen Zweck hinaus erweitert, um BI-Szenarien zu adressieren, sodass Power Query jetzt als einfaches ETL-Tool (Extract Transform Load) verwendet werden kann, mit dem Business-Anwender Geschäftsanwendungen für Microsoft Office 365 und Dynamics 365 entwickeln können, ohne dass hierfür Programmierkenntnisse erforderlich sind.

Ebenfalls im März 2018 hat Microsoft den Begriff Power Query in Power BI Desktop und Excel wieder eingeführt, indem der Titel des Fensters Query-Editor in Power Query-Editor geändert wurde. Um es zu starten, können Sie jetzt im Dropdownmenü Daten abrufen den Befehl Power Query-Editor starten auswählen. Im Juli 2018 gab Microsoft bekannt, dass die Onlineversion von Power Query Teil einer neuen Self-Service-ETL-Lösung, Dataflows, sein wird, mit der Sie Datenvorbereitungen in Power Query problemlos durchführen, die Ergebnisse in Azure speichern und in Power BI oder anderen Anwendungen (https://www.microsoft.com/en-us/businessapplicationssummit/video/BAS2018-2117) nutzen können.

Abbildung 1-3Power Query in CDS für Apps, die im März 2018 angekündigt wurde

Wo finde ich Power Query?

Power Query in Excel und Power BI Desktop zu finden ist eine Herausforderung, wenn Sie nicht wissen, wonach Sie suchen müssen. Während diese Zeilen geschrieben werden, gibt es keinen zentralen Einstiegspunkt mit dem Namen »Power Query«, um den Power Query-Editor zu starten. Abbildung 1-4 fasst die wichtigsten Einstiegspunkte für Power Query in Excel und Power BI Desktop zusammen.

Abbildung 1-4Eine Vielzahl von Einstiegspunkten in Excel und Power BI Desktop können verwendet werden, um Power Query zu starten.

Um in Excel 2010 und 2013 mit dem Importieren von Daten zu beginnen und sie neu zu gestalten, können Sie das Power Query-Add-In von https://www.microsoft.com/de-de/download/details.aspx?id=39379 herunterladen. Dieses Add-In kann sowohl für die allein stehenden Excel-Versionen als auch für Excel in Office 2010 und 2013 verwendet werden. Sobald es installiert ist, wird die Registerkarte Power Query angezeigt. Um mit dem Importieren von Daten zu beginnen, können Sie einen der Konnektoren im Abschnitt Externe Daten abrufen auswählen. Um vorhandene Abfragen zu bearbeiten, klicken Sie auf Bereich anzeigen und wählen die Abfrage aus, die Sie bearbeiten möchten. Alternativ können Sie Editor starten verwenden und die entsprechende Abfrage im Bereich Abfragen auswählen.

Wenn Sie in Excel 2010 und 2013 auf der Registerkarte Daten auf Externe Daten abrufen klicken, gelangen Sie zur klassischen Technologie zum Abrufen von Daten und nicht zu Power Query.

Um in Excel 2016 mithilfe der Power Query-Technologie Daten abzurufen und zu transformieren, können Sie zuerst die Registerkarte Daten überprüfen. Wenn der Abschnitt Abrufen und transformieren angezeigt wird, öffnen Sie das Dropdownmenü Neue Abfrage und wählen dann den Datenquellentyp aus, den Sie verwenden möchten. Wenn Sie eine neuere Version von Excel verwenden, finden Sie auf der Registerkarte Daten die Befehlsgruppe Daten abrufen und transformieren, in der Sie über das Dropdownmenü Daten abrufen mit dem Importieren von Daten beginnen können. Um vorhandene Abfragen zu bearbeiten, können Sie in Excel 2016 Abfragen anzeigen (in den älteren Versionen) oder Abfragen und Verbindungen in der Befehlsgruppe Abfragen und Verbindungen auf der Registerkarte Daten auswählen.

Wenn Sie Excel 2016 verwenden und auf der Registerkarte Daten sowohl die Befehlsgruppe Externe Daten abrufen als auch Abrufen und transformieren sehen, sollten Sie beachten, dass der erste Abschnitt Sie zu den Legacy-Importszenarien führt. Um die Power Query-Technologie zu verwenden, sollten Sie das Dropdownmenü Neue Abfrage in der Gruppe Abrufen und transformieren auswählen. In den neuesten Versionen von Excel 2016, Excel 2019 und Office 365 befindet sich diese Funktionalität im Dropdownmenü Daten abrufen.

In Power BI Desktop können Sie auf der Registerkarte Home den Befehl Daten abrufen auswählen. Anschließend wird das Dialogfeld Daten abrufen geöffnet, in dem Sie Ihre Datenquelle auswählen. Im Dropdownmenü Daten abrufen können Sie eine der häufig verwendeten Quellen auswählen, zum Beispiel Excel, Power BI Service, SQL Server oder Analysis Services. Um Ihre im Bericht vorhandenen Abfragen zu bearbeiten, klicken Sie auf der Registerkarte Home auf Abfragen bearbeiten; hiermit wird der Power Query-Editor gestartet. Sie finden dann alle Abfragen im Bereich Abfragen auf der linken Seite des Power Query-Editors; wählen Sie dort die Abfrage aus, die Sie bearbeiten möchten.

Jetzt kennen Sie die wichtigsten Einstiegspunkte für Power Query. Im nächsten Abschnitt erfahren Sie mehr über die Hauptkomponenten von Power Query.

Hauptkomponenten von Power Query

In diesem Abschnitt lernen Sie die wesentlichen Komponenten von Power Query und die wichtigsten Elemente der Benutzeroberfläche kennen. Hierzu gehören die Schnittstelle zum Abrufen von Daten sowie die Datenverbindungen, der Power Query-Editor und das Dialogfeld Abfrageoptionen.

Daten abrufen und Konnektoren

Das Herstellen einer Verbindung zu einer Datenquelle ist der erste Schritt im Lebenszyklus beim Erstellen eines Berichts. Mit Power Query können Sie Verbindungen zu einer Vielzahl von Datenquellen herstellen. Häufig werden Datenquellen auch Datenverbindungen oder Konnektoren genannt. Wenn Sie beispielsweise in Excel Daten abrufen auswählen, dann Aus Datenbank und abschließend Aus SQL Server-Datenbank anklicken, wählen Sie in Power Query den SQL Server-Konnektor aus. Die Liste der unterstützten Datenverbindungen wird häufig im Monatsrhythmus über Power BI Desktop-Updates aktualisiert und dann später in Excel in Office 365 und im Power Query-Add-In für Excel 2010 und 2013 eingepflegt.

Um in Excel die derzeit unterstützten Datenquellen anzuzeigen, öffnen Sie auf der Registerkarte Daten das Menü der Schaltfläche Daten abrufen und sehen Sie sich die verschiedenen Optionen unter Aus Datei, Aus Datenbank, Aus Azure, Aus Onlinediensten und Aus anderen Quellen an, wie in Abbildung 1-5 dargestellt.

Abbildung 1-5Für den Import können Sie eine Vielzahl von Konnektoren verwenden.

Viele Konnektoren werden zuerst in Power BI Desktop veröffentlicht, finden jedoch nicht sofort ihren Weg in Excel. Dies kann mit der Ausgereiftheit des Konnektors zusammenhängen, der Verbreitung der Datenquelle oder aus zwischen Microsoft und dem Anbieter der Datenquelle getroffenen Vereinbarungen resultieren. Darüber hinaus werden die folgenden Datenquellen in Excel angezeigt, wenn Sie Excel als Einzelanwendung oder Office Pro Plus beziehungsweise Office Professional verwenden:

Datenbanken: Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata und SAP Hana

Azure: Azure SQL Server, Azure SQL Data Warehouse, Azure HDInsight (HDFS), Azure Blob Storage, Azure Table und Azure Data Lake Store

Andere Quellen: SharePoint, Active Directory, Hadoop, Exchange, Dynamics CRM und Salesforce

Datenkatalog: Datenkatalogsuche und Meine Datenkatalogabfragen

Weitere Informationen zu Konnektoren finden Sie unter https://support.office.com/de-de/article/wo-befindet-sich-a0-transformation-power-query-e9332067-8e49-46fc-97ff-f2e1bfa0cb16.

In Power BI Desktop können Sie auf Daten abrufen klicken, um das Dialogfeld Daten abrufen zu öffnen. Von dort aus können Sie nach dem Konnektor suchen, den Sie verwenden möchten, oder durch die Ansichten Alle, Datei, Datenbank, Power Platform, Azure, Onlinedienste und Sonstige navigieren, um den gewünschten Konnektor zu finden. Eine vollständige Liste der Datenquellen in Power BI Desktop finden Sie unter https://docs.microsoft.com/de-de/power-bi/desktop-data-sources.

Wenn Sie eine bereits vorhandene Datenquelle wiederverwenden möchten, müssen Sie nicht erneut die Schritte der Schnittstelle Daten abrufen durchlaufen. Stattdessen können Sie in Excel auf der Registerkarte Daten im Abschnitt Daten abrufen und transformieren auf Zuletzt verwendete Quellen klicken oder die gleiche Schaltfläche auf der Registerkarte Home von Power BI Desktop verwenden. Im Dialogfeld Zuletzt verwendete Quellen finden Sie die spezifischen Datenquellen, die Sie zuletzt verwendet haben. Sie können dort Ihre bevorzugten Quellen anheften, damit diese immer oben in der Liste des Dialogfelds Zuletzt verwendete Quellen angezeigt werden.

Viele der Datenquellen, mit denen Sie eine Verbindung herstellen, zum Beispiel Datenbanken und Dateien in SharePoint, bieten integrierte Authentifizierungsmethoden. Die von Ihnen angegebenen Anmeldeinformationen werden nicht im Bericht selbst, sondern auf Ihrem Computer gespeichert. Um die Anmeldeinformationen zu bearbeiten oder die Authentifizierungsmethode zu ändern, können Sie die Datenquelleneinstellungen über die Registerkarte Home des Power Query-Editors starten oder auf der Registerkarte Datei den Befehl Optionen & Einstellungen verwenden. Wenn das Dialogfeld Datenquelleneinstellungen geöffnet wird, können Sie die Datenquelle auswählen und die Anmeldeinformationen zurücksetzen. Weitere Informationen zu Datenquelleneinstellungen finden Sie unter https://support.office.com/de-de/article/Data-Source-settings-power-query-9f24a631-f7eb-4729-88dd-6a4921380ca9.

Die Hauptbereiche des Power Query-Editors

Nachdem Sie eine Verbindung zu einer Datenquelle hergestellt haben, wird in der Regel das Dialogfeld Navigator angezeigt. Im Navigator wählen Sie die Tabellen aus, die Sie aus der Datenquelle laden möchten, oder Sie können einfach eine Vorschau der Daten erhalten. (Sie werden den Navigator in Übung 1-1 kennenlernen.) Im Navigator können Sie Daten transformieren auswählen, um in das Herz und den Mittelpunkt von Power Query einzutreten: den Power Query-Editor. Hier wird im Hauptfenster eine Vorschau der Daten anzeigt und Sie können die Daten untersuchen und mit der Durchführung von Datentransformationen beginnen. Wie in Abbildung 1-6 dargestellt, besteht der Power Query-Editor aus den folgenden Komponenten: Vorschaubereich, Menüband, Bereich Abfragen, Bereich Abfrageeinstellungen, Bereich Angewendete Schritte und der Bearbeitungsleiste. Lassen Sie mich die verschiedenen Elemente kurz erläutern.

Abbildung 1-6Der Power Query-Editor enthält eine Reihe von Benutzeroberflächenkomponenten.

Der Vorschaubereich

Im Vorschaubereich, der als zentraler Bereich in Abbildung 1-6 dargestellt ist, können Sie eine Vorschau Ihrer Daten anzeigen und sie untersuchen und vorbereiten, bevor Sie sie in einen Bericht übernehmen. Normalerweise werden Daten in diesem Bereich in einem tabellarischen Format angezeigt. Über die Spaltenüberschriften können Sie bestimmte Transformationen initiieren, zum Beispiel das Umbenennen oder Entfernen von Spalten. Sie können außerdem Filter auf Spalten anwenden, indem Sie das Filtersteuerelement in den Spaltenüberschriften verwenden.

Der Vorschaubereich ist kontextabhängig. Dies bedeutet, dass Sie mit der rechten Maustaste auf ein beliebiges Element in der Tabelle klicken können, um ein Kontextmenü zu öffnen, das die Transformationen enthält, die auf das ausgewählte Element angewendet werden können. Wenn Sie beispielsweise mit der rechten Maustaste auf die obere linke Ecke der Tabelle klicken, sind Transformationen auf Tabellenebene verfügbar, zum Beispiel Erste Zeile als Überschriften verwenden.

Mithilfe der Kontextmenüs im Vorschaubereich des Power Query-Editors können Sie neue Transformationen entdecken und die Features von Power Query erkunden.

Denken Sie daran, dass im Vorschaubereich nicht immer das gesamte Dataset angezeigt wird. Der Vorschaubereich wurde entwickelt, um nur einen Teil der Daten anzuzeigen und Ihnen die Datenvorbereitung mit großen Datasets zu ermöglichen. Bei breiten (viele Spalten) oder großen (viele Zeilen) Datasets können Sie die Daten überprüfen, indem Sie im Vorschaubereich nach links und rechts scrollen, oder Sie können den Bereich Filter öffnen, um die eindeutigen Werte in jeder Spalte zu überprüfen.

Neben der Datenexploration ist die Spaltenauswahl die häufigste Aktion, die Sie im Vorschaubereich vornehmen. Sie können eine oder mehrere Spalten im Vorschaufenster auswählen und dann auf die ausgewählten Spalten eine Transformation anwenden. Wenn Sie mit der rechten Maustaste auf eine Spaltenüberschrift klicken, werden die entsprechenden Schritte zur Spaltentransformation angezeigt, die im Kontextmenü verfügbar sind. Beachten Sie, dass Spalten über Datentypen verfügen und die Transformationen, die Ihnen über das Kontextmenü und die Registerkarten im Menüband zur Verfügung stehen, vom Datentyp der Spalte abhängen.

Das Menüband

Gemäß dem Look & Feel von Microsoft Office enthält auch der Power Query-Editor ein Menüband mit mehreren Registerkarten, wie in Abbildung 1-7 dargestellt. Jede Registerkarte enthält eine Vielzahl von Transformationsschritten oder anderen Aktionen, die auf Abfragen angewendet werden können. Sehen wir uns die einzelnen Registerkarten an:

Datei:

Auf dieser Registerkarte speichern Sie einen Bericht, schließen den Power Query-Editor und öffnen die Dialogfelder

Abfrageoptionen

sowie

Datenquelleneinstellungen

.

Home:

Auf dieser Registerkarte finden Sie einige der häufigsten Transformationsschritte, zum Beispiel

Spalten auswählen, Spalten entfernen, Zeilen beibehalten

und

Zeilen entfernen

. Sie können auch den Vorschaubereich aktualisieren und den Abfrage-Editor schließen. Der Befehl

Neue Quelle

entspricht dem Befehl

Daten abrufen

in Excel und Power BI Desktop; mit ihm können Sie neue Datenquellen als zusätzliche Abfragen importieren.

Abbildung 1-7Im Menüband des Power Query-Editors finden Sie mehrere nützliche Registerkarten.

Sie können im Power Query-Editor an mehreren Abfragen arbeiten. Jede Abfrage kann als separate Tabelle geladen oder von einer anderen Abfrage verwendet werden. Das Kombinieren mehrerer Abfragen ist eine äußerst leistungsfähige Funktionalität, die in Kapitel 3, »Daten aus mehreren Quellen kombinieren«, vorgestellt wird.

Transformieren:

Auf dieser Registerkarte können Sie eine Transformation auf ausgewählte Spalten anwenden. Je nach Datentyp der Spalte werden einige Befehle aktiviert oder deaktiviert. Wenn Sie zum Beispiel eine Datumsspalte auswählen, werden die datumsbezogenen Befehle aktiviert. Auf dieser Registerkarte finden Sie auch sehr nützliche Transformationen wie

Gruppieren nach, Erste Zeile als Überschriften verwenden, Überschriften als erste Zeile verwenden

sowie

Vertauschen

(Transponieren).

Spalte hinzufügen:

Auf dieser Registerkarte fügen Sie einer Tabelle neue Spalten hinzu, indem Sie auf ausgewählte Spalten Transformationen anwenden. Mit zwei speziellen Befehlen erreichen Sie komplexe Transformationen in neuen Spalten über eine sehr einfache Benutzeroberfläche. Diese Befehle,

Spalte aus Beispielen

und

Bedingte Spalte

, werden im weiteren Verlauf des Buches ausführlicher erläutert und veranschaulicht. Auf dieser Registerkarte können fortgeschrittene Benutzer benutzerdefinierte Spalten und benutzerdefinierte Funktionen aufrufen, was ebenfalls in späteren Kapiteln erläutert wird.

Ansicht:

Auf dieser Registerkarte ändern Sie die Ansichtseinstellungen des Power Query-Editors. Sie können beispielsweise die Bearbeitungsleiste einschalten, zu einer bestimmten Spalte navigieren (was sehr nützlich ist, wenn Ihre Tabelle Dutzende von Spalten enthält) und das Fenster öffnen, das die Abfrageabhängigkeiten darstellt.

Im Verlauf dieses Buches werden Sie anhand von praktischen Übungen, die reale Datenherausforderungen simulieren, die am häufigsten verwendeten und nützlichsten Befehle im Power Query-Editor kennenlernen.

Der Bereich Abfragen

Im Bereich Abfragen, der sich auf der linken Seite des Power Query-Editors befindet (siehe Abbildung 1-6), wählen Sie die Abfrage aus, die Sie bearbeiten möchten, oder erstellen neue Abfragen, indem Sie eine der Abfragen duplizieren oder einen Verweis auf eine vorhandene Abfrage erstellen. Wenn Sie im Bereich Abfragen mit der rechten Maustaste auf eine Abfrage klicken, können Sie die verschiedenen Vorgänge untersuchen, die Sie auf die Abfrage anwenden können.

Sie organisieren Abfragen im Bereich Abfragen, indem Sie sie in Abfragegruppen zusammenfassen. Diese Gruppen haben keine Auswirkungen auf die zugrunde liegenden Daten oder einen Bericht. Sie dienen nur als visuelle Ordner im Bereich Abfragen. Sie können auch die Reihenfolge der Abfragen und Gruppen ändern, indem Sie Elemente im Bereich nach oben oder unten verschieben.

Wenn Sie in Excel den Power Query-Editor starten, wird manchmal der Abfragebereich reduziert dargestellt. Sie können ihn bei Bedarf erweitern. Nachdem Sie den Power Query-Editor geschlossen haben, können Sie die Abfragen in Excel über den Aufgabenbereich Abfragen und Verbindungen verwalten.

Abfrageeinstellungen und Angewendete Schritte

Im Bereich Abfrageeinstellungen auf der rechten Seite des Power Query-Editors benennen Sie eine Abfrage um, öffnen das Dialogfeld Abfrageeigenschaften, um eine Beschreibung bereitzustellen, und verwalten die Transformationsschritte. Im Bereich Angewendete Schritte überprüfen Sie die Transformationsschritte einer Abfrage.

Mit Power Query können Sie eine Sequenz von Transformationen für importierte Daten erstellen, bevor die Daten in einem Bericht landen. Wenn Sie Transformationsschritte anwenden, werden diese Schritte im Bereich Angewendete Schritte hinzugefügt. Sie können jederzeit einen der Schritte unter Angewendete Schritte auswählen, einen Schritt ändern oder zwischen zwei vorhandenen Schritten und am Ende der Sequenz einen neuen Schritt einfügen.

Die Bearbeitungsleiste, der erweiterte Editor und die M-Abfragesprache

Standardmäßig ist die Bearbeitungsleiste im Power Query-Editor deaktiviert. Sie aktivieren sie auf der Registerkarte Ansicht, indem Sie das Kontrollkästchen Bearbeitungsleiste einschalten. Obwohl Sie die Bearbeitungsleiste in vielen Datentransformationsszenarien nicht verwenden müssen, werden Sie in diesem Buch sehen, dass es viele Szenarien gibt, in denen die Bearbeitungsleiste sehr hilfreich ist.

Während in der Bearbeitungsleiste in Excel Excel-Formeln anzeigt werden, zeigt die Bearbeitungsleiste im Power Query-Editor M-Formeln an. Jeder Transformationsschritt, den Sie im Power Query-Editor erstellen, beginnend mit dem ersten Import der Daten, generiert in dieser Leiste eine Formel. Diese Formel ist Teil der M-Abfragesprache, einer speziellen Programmiersprache, die von Microsoft für Power Query entwickelt wurde und die es Ihnen ermöglicht, die Transformationsfunktionen dieses Tools zu erweitern.

Auf der Registerkarte Home oder auf der Registerkarte Ansicht starten Sie den erweiterten Editor, der den gesamten M-Ausdruck anzeigt, der durch die Schritte generiert wurde, die Sie im Power Query-Editor ausgeführt haben. Von hier aus können fortgeschrittene Benutzer den Ausdruck anpassen, um komplexe Transformationen zu erstellen. Für die meisten Übungen in diesem Buch müssen Sie den erweiterten Editor nicht öffnen. Wenn Sie jedoch neugierig auf M sind, starten Sie den erweiterten Editor und untersuchen Sie die M-Syntax Ihrer Abfragen. Sie werden M in den Übungen in diesem Buch kennenlernen, wenn eine Anpassung des Codes in der Bearbeitungsleiste oder im erweiterten Editor Ihnen hilft, spezielle Datenherausforderungen zu lösen. Kapitel 9 gibt Ihnen die Möglichkeit, tiefer in M einzusteigen und sich auf die Kernsyntax und die integrierten Funktionen zu konzentrieren.

Auch wenn die Programmierung nicht Ihr Ding ist, besteht kein Grund zur Sorge. Für die meisten Herausforderungen in diesem Buch ist es nicht erforderlich, dass Sie M verwenden.

Abfrageoptionen in Excel und Optionen in Power BI

Im Dialogfeld Abfrageoptionen in Excel und im Dialogfeld Optionen in Power BI Desktop legen Sie verschiedene Einstellungen für Power Query fest. In Excel werden die Optionen in den Abschnitten Global und Aktuelle Arbeitsmappe zusammengefasst. In Power BI Desktop sind die Optionen in den Abschnitten Global und Aktuelle Datei gruppiert. Einige der Optionen sind nur in Excel verfügbar. Andere Optionen finden Sie nur in Power BI Desktop. Das letztgenannte Produkt enthält zusätzliche Einstellungen für Power BI Desktop, die über die von Power Query hinausgehen.

Die globalen Optionen, die auf Ihrem Computer gespeichert werden, wirken sich auf alle Berichte aus, die Sie von diesem Computer aus erstellen. Die Optionen in der Kategorie Aktuelle Arbeitsmappe beziehungsweise Aktuelle Datei werden in der Datei gespeichert und nicht an andere Dateien weitergegeben. Wie in Abbildung 1-8