M E-Book

Ignaz A. Schels

• Herausgeber: Carl Hanser Verlag GmbH & Co. KG
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Deutsch
• Veröffentlichungsjahr: 2023

Seitenzahl: 389

Beschreibung

- Automatisierter Datenimport
- 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

Leseprobe

Ignaz A. Schels

M

Daten abfragen und verarbeiten mit Excel und Power BI

2., überarbeitete Auflage

Der Autor:Ignaz A. Schels, München

Alle in diesem Werk enthaltenen Informationen, Verfahren und Darstellungen wurden nach bestem Wissen zusammengestellt und mit Sorgfalt getestet. Dennoch sind Fehler nicht ganz auszuschließen. Aus diesem Grund sind die im vorliegenden Werk enthaltenen Informationen mit keiner Verpflichtung oder Garantie irgendeiner Art verbunden. Autor und Verlag übernehmen infolgedessen keine juristische Verantwortung und werden keine daraus folgende oder sonstige Haftung übernehmen, die auf irgendeine Art aus der Benutzung dieser Informationen – oder Teilen davon – entsteht.Ebenso wenig übernehmen Autor und Verlag keine Gewähr dafür, dass beschriebene Verfahren usw. frei von Schutzrechten Dritter sind. Die Wiedergabe von Gebrauchsnamen, Handelsnamen, Warenbezeichnungen usw. in diesem Werk berechtigt also auch ohne besondere Kennzeichnung nicht zu der Annahme, dass solche Namen im Sinne der Warenzeichen- und Markenschutz-Gesetzgebung als frei zu betrachten wären und daher von jedermann benutzt werden dürften.

Bibliografische Information der Deutschen Nationalbibliothek:Die Deutsche Nationalbibliothek verzeichnet diese Publikation in der Deutschen Nationalbibliografie; detaillierte bibliografische Daten sind im Internet über http://dnb.d-nb.de abrufbar.

Dieses Werk ist urheberrechtlich geschützt.Alle Rechte, auch die der Übersetzung, des Nachdruckes und der Vervielfältigung des Buches, oder Teilen daraus, vorbehalten. Kein Teil des Werkes darf ohne schriftliche Genehmigung des Verlages in irgendeiner Form (Fotokopie, Mikrofilm oder ein anderes Verfahren), auch nicht für Zwecke der Unterrichtsgestaltung – mit Ausnahme der in den §§ 53, 54 URG genannten Sonderfälle – reproduziert oder unter Verwendung elektronischer Systeme verarbeitet, vervielfältigt oder verbreitet werden.

© 2023 Carl Hanser Verlag München, http://www.hanser-fachbuch.deLektorat: Brigitte Bauer-SchiewekCopy editing: Petra Kienle, FürstenfeldbruckUmschlagdesign: Marc Müller-Bremer, München, www.rebranding.deUmschlagrealisation: Max KostopoulosSatz: Eberl & Koesel Studio, Kempten

Print-ISBN:         978-3-446-47760-5E-Book-ISBN:    978-3-446-47811-4E-Pub-ISBN:       978-3-446-47944-9

Inhalt

Titelei

Impressum

Inhalt

Vorwort

1 Grundlagen der Abfrage-Logik

2 Abrufen: Verbindungen zu externen Quellen herstellen

2.1 Einfache Abfrage auf Excel-Datei

2.2 Aktualisieren der Abfrage

2.3 Abfragen automatisch aktualisieren

2.4 Kann man Abfragen separat speichern?

3 Transformieren: Aufbereiten der abgerufenen Daten

3.1 Der Power Query-Editor

3.2 Transformationsschritte löschen

3.3 Zeilen filtern

3.4 Überschriften (Header) einfügen

3.5 Spalten entfernen

3.6 Werte ersetzen

3.7 Datentypen bestimmen

3.8 Transformationsschritte prüfen und benennen

3.9 Abschließen der Transformation

3.10 Erneutes Bearbeiten der Abfrage

4 Erweiterte Transformationen

4.1 Nach unten bzw. oben ausfüllen

4.2 Zeilen und Spalten vertauschen (Transponieren)

4.3 Spalten verbinden

4.4 Entpivotieren

4.5 Spalten teilen und Abschluss der Transformation

5 Anpassen der Rohdaten

5.1 Texttransformationen

5.2 Datumstransformationen

5.3 Zahlentransformationen

5.4 Gruppieren

6 Abfragen auf Webseiten

6.1 Webabfrage auf eine Tabelle

6.2 Webabfrage ohne Tabellen

6.2.1 Das Element Document

6.2.2 Untersuchen des HTML-Codes

6.2.3 Den Text der Homepage direkt auswerten

7 Abfragen auf relationale Datenbanken

7.1 Beispiel mit Oracle-Datenbank

7.2 Query Folding

8 Mehrere Quellen kombinieren

8.1 Abfragen anfügen

8.2 Abfragen zusammenführen

8.3 Alle Dateien in einem Ordner einlesen

9 Spalten hinzufügen – erste Schritte mit M

9.1 Bedingte Spalten

9.2 Datums- und Rechenfunktionen

9.3 Benutzerdefinierte Spalten

9.3.1 Spaltenformeln für Berechnungen eingeben

9.3.2 Bedingungslogik mit if

9.3.3 M-Funktionen

10 Abfragecode bearbeiten mit M

10.1 Die Bearbeitungsleiste

10.2 Das Editor-Fenster

10.3 Erstellen einer leeren Abfrage

10.4 Die Grundstruktur des Abfrage-Codes

10.5 Schritte und Schrittnamen verstehen

10.6 Fehler im M-Code vermeiden

10.7 Schritte zusammenfassen

10.8 Kommentare

11 Werte und Datentypen

11.1 Einfache Datentypen

11.1.1 Null

11.1.2 Logical (true/false)

11.1.3 Number (Zahl)

11.1.4 Time (Zeit)

11.1.5 Date (Datum)

11.1.6 Datetime (Datum/Uhrzeit)

11.1.7 Datetimezone (Datum/Uhrzeit/Zeitzone)

11.1.8 Duration (Dauer)

11.1.9 Text

11.1.10 Binary (Binär)

11.2 Übergeordnete Datentypen

11.2.1 List (Liste)

11.2.2 Record (Datensatz)

11.2.3 Table (Tabelle)

11.3 Spezielle Datentypen

11.3.1 Type (Datentyp)

11.3.2 Function (Funktion)

12 Abfragen flexibel gestalten

12.1 Nicht-lineare Abfragen

12.2 Unterschiedliche Tabellenzeilen vergleichen

12.2.1 Zeilensprung über den Index

12.2.2 Schnellerer Zeilensprung mit Index und Join

12.3 Abfragen mit Parametern

12.3.1 Power-Query-Parameter

12.3.2 Parameter aus anderen Quellen

13 Fehlerbehandlung

13.1 Fehler vorbeugen

13.2 Error-Werte entfernen

13.3 Fehler abfangen mit try

13.4 Fehlermeldungen erzeugen

14 M-Funktionen

14.1 Das Syntax-Schema

14.2 Abruf der Funktionsliste

14.3 Listenfunktionen

14.3.1 Listenerstellung

14.3.2 Informationen über Listenelemente

14.3.3 Berechnungen

14.3.4 Ordnung und Reihenfolge

14.3.5 Auswahl

14.3.6 Transformationen

14.3.7 Vergleiche mehrerer Listen

14.4 Datensatzfunktionen

14.4.1 Erstellung und Umwandlung

14.4.2 Informationen über Datensätze

14.4.3 Auswahl

14.4.4 Transformationen

14.5 Tabellenfunktionen

14.5.1 Informationen über Tabellen

14.5.2 Umwandlungen

14.5.3 Spalten

14.5.4 Zeilen

14.5.5 Ordnung und Sortierung

14.5.6 Erstellung

14.5.7 Transformationen

14.5.8 Arbeiten mit mehreren Tabellen

14.5.9 Ähnlichkeiten erkennen (Fuzzy-Funktionen)

14.5.10 Sonstige

14.6 Textfunktionen

14.6.1 Erstellung und Konvertierung

14.6.2 Informationen über Texte

14.6.3 Auszüge von Textteilen

14.6.4 Modifikationen

14.6.5 Transformationen

14.7 Zahlenfunktionen

14.7.1 Informationen über Zahlen

14.7.2 Rechenoperationen

14.7.3 Rundung

14.7.4 Zufallszahlen

14.7.5 Trigonometrie

14.7.6 Konvertierung und Formatierung

14.8 Logical-Funktionen

14.9 Datumsfunktionen

14.9.1 Erzeugung und Umwandlung

14.9.2 Informationen über Datumswerte

14.9.3 Berechnungen

14.9.4 Vergleiche mit 'Jetzt'

14.10 DateTime-Funktionen

14.10.1 Erzeugung und Umwandlung

14.10.2 Vergleiche mit ‚Jetzt‘

14.11 DateTimeZone-Funktionen

14.12 Duration-Funktionen

14.13 Zeitfunktionen

14.14 Hilfsfunktionen

14.14.1 Comparer-Funktionen

14.14.2 Replacer-Funktionen

14.14.3 Combiner-Funktionen

14.14.4 Splitter-Funktionen

15 Eigene Funktionen erstellen

15.1 Funktionen innerhalb einer Abfrage

15.2 Das Schlüsselwort each

15.3 Funktionen als eigene Abfrage

15.4 Funktionen, die aus mehreren Schritten bestehen

15.5 Rekursive Funktionen

15.6 Funktionsbeschreibungen in der Metadata

15.7 Funktionen teilen

15.7.1 Aktivierung und Vorbereitung

15.7.2 Funktionserweiterungen

16 Die KI macht’s möglich

16.1 Spaltenformeln aus Beispielen erstellen

16.2 KI-Funktionen (in Power BI mit Premium-Lizenz)

16.2.1 Textanalyse mit KI-Insights

16.2.2 Bildanalyse mit KI-Insights

17 M und VBA

17.1 Abfragen per VBA-Befehl aktualisieren

17.1.1 Schaltflächen zum Aktualisieren nutzen

17.1.2 Aktualisieren bei Ereignissen

17.2 Abfrage-Code per VBA anpassen

17.3 Abfrageergebnisse mit VBA auslesen

18 Excel-Datentypen aus Power Query

18.1 Eigenen Datentyp per Abfrage erzeugen

18.2 Datentypen aus Power BI-Datasets für die ganze Organisation

19 Datenschutz und Firewall

19.1 Datenschutz bei verschiedenen Datenquellen

19.2 Der Formula.Firewall-Fehler

20 Tipps für mehr Geschwindigkeit

20.1 Schnelleres Entwerfen der Abfrage

20.2 Schnelleres Aktualisieren

20.3 Messen der Geschwindigkeit

20.3.1 Power BI: Nutzen der Diagnose-Werkzeuge

20.3.2 Excel: Messen der Zeit über VBA

20.3.3 Auswertung der Trace-Datei

Vorwort

Es mag ungewöhnlich erscheinen, ein Fachbuch zu schreiben, das nur einen Teilbereich von zwei so vielseitigen Programmen wie Excel und Power BI Desktop behandelt. Warum nicht stattdessen ein Buch, das entweder Excel oder Power BI in allen Facetten erklärt?

Wenn Sie dieses Buch gekauft haben, ahnen Sie zumindest, welches Potenzial in den Features schlummert, die unter dem Namen Power Query zusammengefasst werden. Excel-Nutzer verbringen unzählige Stunden damit, Daten aus den verschiedensten Quellen zusammenzukopieren und dabei händisch zu modellieren. Etliche Power-BI-Nutzer wissen zwar, wie Daten abgerufen werden, stoßen aber schnell an ihre Grenzen, wenn die Datenbasis nicht richtig aufbereitet ist. Wenn Sie nicht zu diesen Benutzern gehören wollen, haben Sie das richtige Buch gefunden.

Das Thema des vorliegenden Buchs

Was als Add-In für Excel begann, ist nun fester Teil von einer wachsenden Menge an Microsoft-Produkten, allen voran Excel und Power BI Desktop. Die Funktionsweise der Abfragen und die Benutzeroberfläche des Abfrage-Editors sind dabei bis auf wenige Ausnahmen programmübergreifend gleich. Deshalb ist es durchaus möglich, Power Query sowohl für Excel als auch Power BI Desktop zu erklären.

Aus genau diesem Grund findet aber auch eine starke Beschränkung der abgedeckten Themen statt: Das Buch behandelt nur das Abrufen von externen Daten und die Bearbeitung (Transformation) im Power Query-Editor, basierend auf der Abfragesprache M. Sie werden lernen, wie Sie Daten in Ihr Programm laden und dabei genau in die Form bringen, in der Sie sie benötigen. Die so aufbereiteten Tabellen können dann in Excel und Power BI nach Belieben interpretiert und visualisiert werden – doch das ist nicht mehr Thema des Buchs.

Unterschiede zwischen den Versionen

Die Grundlagen der Abfrage-Logik und der Formelsprache M haben sich seit den ersten Versionen von Power Query kaum verändert. Die Benutzeroberfläche und die Entwicklungsumgebung des Abfrage-Editors werden jedoch laufend optimiert.

Die regelmäßige Verbesserung eines Programms ist natürlich zu begrüßen, doch erschwert sie das Lernen und Lehren der Grundlagen. Es ist nicht auszuschließen, dass das eine oder andere Beispiel auf Ihrem Computer nicht genauso funktioniert wie hier beschrieben – sei es, dass Sie eine ältere Version benutzen oder sei es, dass nach der Buchveröffentlichung weitere Neuerungen eingeführt wurden.

Seien Sie aber bitte nicht frustriert, wenn eine Beschreibung oder eine Abbildung nicht exakt zutrifft. In den meisten Fällen ist das entsprechende Werkzeug nur anders benannt oder befindet sich an einer anderen Stelle. Spätestens wenn es um das Schreiben von M-Code geht, sind Versionsunterschiede ohnehin kaum mehr von Belang.

Kontakt zum Autor

Auch bei einem so starren Medium wie einem Buch darf der Kontakt zwischen Leser und Autor nicht zu kurz kommen. Scheuen Sie sich nicht, mir zu schreiben, wenn Sie Fragen zu einem der behandelten Themen haben. Ein Software-Spezialist lebt von solchen Herausforderungen. Feedback jeder Art, egal ob Lob oder Kritik, ist natürlich auch immer willkommen. Schicken Sie einfach eine Mail an [email protected].

Der Verlag und die Autoren haben sich mit der Problematik einer gendergerechten Sprache intensiv beschäftigt. Um eine optimale Lesbarkeit und Verständlichkeit sicherzustellen, wird in diesem Werk auf Gendersternchen und sonstige Varianten verzichtet; diese Entscheidung basiert auf der Empfehlung des Rates für deutsche Rechtschreibung. Grundsätzlich respektieren der Verlag und die Autoren alle Menschen unabhängig von Ihrem Geschlecht, ihrer Sexualität, ihrer Hautfarbe, ihrer Herkunft und ihrer nationalen Zugehörigkeit.

1Grundlagen der Abfrage-Logik

Der ursprüngliche Name der hier behandelten Technologie, Power Query, wird in aktuellen Microsoft-Produkten fast nicht mehr verwendet. Stattdessen sieht man je nach Programm und Version meist verschiedene Varianten der Begriffsgruppe „Daten abrufen und transformieren“ (englisch „Get & Transform Data“). Diese Bezeichnung ist zwar etwas sperriger, beschreibt aber treffend die zwei Schritte, in die sich jede Abfrage aufteilen lässt:

1.      Abrufen: Eine externe Datenquelle, z. B. eine Excel-Arbeitsmappe, eine Textdatei oder eine Datenbank, wird „angezapft“ und die enthaltenen Informationen werden eingelesen.

2.      Transformieren: Wenn die Daten abgerufen sind, werden sie in die gewünschte Form gebracht. Dabei können zum Beispiel die benötigten Zeilen und Spalten ausgewählt, Formate angepasst oder Sortierungen vorgenommen werden. Auch Ersetzungen oder Berechnungen sind in diesem Schritt schon möglich.

Das Ergebnis einer Power-Query-Abfrage ist im Normalfall eine Tabelle. Diese kann wiederum als Basis für weitere Auswertungen oder Visualisierungen verwendet werden.

Wenn Sie bereits etwas Erfahrung mit dem Erstellen von Diagrammen, Pivot-Tabellen oder anderen Visualisierungen in Excel und Power BI haben, wissen Sie, dass eine saubere Datenbasis dabei unerlässlich ist. Damit eine Visualisierung das zeigt, was sie soll, müssen die Ausgangsdaten in der entsprechenden Form sein. Schon eine Zwischenüberschrift oder ein Zeilenumbruch kann alles durcheinanderbringen.

Die Möglichkeit, die eingelesenen Daten direkt zu transformieren, kann also ein wahrer Segen sein: Egal in welcher Form die Quelldaten vorliegen, Sie können in der Abfrage definieren, welche Daten Sie benötigen und wie sie angepasst werden sollen. Wenn sich die Quelldaten ändern, liest die Abfrage die neuen Daten ein und führt damit die festgelegten Transformierungsschritte durch.

Die Quelldaten werden durch eine Abfrage nicht verändert.

Falls Sie mehrere Datenquellen gemeinsam auswerten wollen, ist das auch kein Problem: Abfragen können miteinander verbunden werden. Das Ergebnis einer Abfrage kann auch die Basis einer weiteren Abfrage sein. Ihre Quelldaten müssen nicht einmal immer am gleichen Ort liegen: Sie können beispielsweise bestimmte Dateien innerhalb eines Ordners auswählen oder Parameter für die Quelladresse festlegen.

In den folgenden Kapiteln erhalten Sie einen Einblick in die vielen Möglichkeiten, die eine Abfrage mit Power Query bietet. Die meisten Techniken werden anhand von Beispielen gezeigt, die Sie Schritt für Schritt nachvollziehen können. Die zugehörigen Beispieldateien finden Sie zum Download unter dieser Adresse:

www.schels.de/publikationen

2Abrufen: Verbindungen zu externen Quellen herstellen

Um zu sehen, welche Vielzahl an Datenquellen für die Abfrage zur Verfügung stehen, verschaffen Sie sich am besten selbst einen Überblick.

EXCEL:

Um eine neue Abfrage in Excel (ab Version 2016) zu erstellen, wählen Sie das Register DATEN und klicken Sie unter der Rubrik Daten abrufen und Transformieren auf DATEN ABRUFEN. Daraufhin können Sie Ihre Datenquelle auswählen.

Bei Excel-Versionen vor Januar 2018 ist die Schaltfläche mit NEUE ABFRAGE beschriftet.

Bei den Excel-Versionen vor Excel 2016 sind die Abfrage-Tools noch nicht integriert. Nutzer von Excel 2010 und 2013 müssen aber nicht das Nachsehen haben: Die Werkzeug-Rubrik Abrufen und Transformieren ist lediglich die Weiterentwicklung eines Add-Ins, das schon ab Excel 2010 verfügbar ist. Das Add-In Power Query basiert auf den gleichen grundlegenden Methoden wie die späteren, integrierten Werkzeuge. Es gibt jedoch Unterschiede, etwa bei den Bezeichnungen und Positionen einzelner Features. Mit leichten Abwandlungen können aber fast alle Beispiele in diesem Buch auch von Nutzern der älteren Excel-Versionen nachvollzogen werden.

Den Download-Link für das Add-In und Hinweise zur Installation gibt es auf der Seite von Microsoft:

https://www.microsoft.com/en-us/download/details.aspx?id=39379

POWER BI:

In Power BI Desktop haben Sie zwei Möglichkeiten, eine Abfrage zu erstellen: Gleich bei Programmstart erscheint ein kleines Begrüßungsfenster, in dem Sie auf der linken Seite DATEN ABRUFEN anklicken können. Alternativ finden Sie auch im Programmfenster auf dem Register START eine Schaltfläche DATEN ABRUFEN. In beiden Fällen gelangen Sie zu einem Fenster, in dem Sie Ihre Datenquelle auswählen können.

Bild 2.1Erstellen einer Abfrage in Excel.

Die Auswahl der Datenquellen ist beträchtlich: Neben einfachen Excel- und Textdateien können Sie auf die meisten gängigen Datenbanksysteme zugreifen. Microsofts Sharepoint und Azure werden natürlich ebenfalls unterstützt. Sie können sogar Daten von Exchange-Servern oder Webseiten auslesen.

Das Vorgehen ist für die verschiedenen Datenquellen ähnlich und meist selbsterklärend: Zunächst wählen Sie den Speicherort oder die Adresse Ihrer Quelle aus. Je nach Art der Verbindung können zusätzlich noch weitere Informationen wie z. B. Zugangsdaten oder Teilbereiche der jeweiligen Datei oder Datenbank ausgewählt werden. Zum Abschluss haben Sie die Möglichkeit, die Daten unverändert einzulesen oder vorher im Abfrage-Editor zu bearbeiten. Der folgende Abschnitt zeigt eine einfache Abfrage ohne Transformation am Beispiel einer Excel-Datei.

Bild 2.2Auswahl der Datenquelle in Power BI Desktop.

2.1Einfache Abfrage auf Excel-Datei

Das erste Beispiel können Sie, wie fast alle Beispiele dieses Buchs, gleichermaßen mit Excel und Power BI Desktop nachvollziehen. Die Arbeitsschritte sind meistens für beide Programme die gleichen. Falls es Unterschiede gibt, ist dies im Text hervorgehoben.

Die Übungsdateien zu diesem Buch können Sie unter folgender Adresse herunterladen:

www. schels.de/publikationen

       EXCEL: Öffnen Sie eine neue Arbeitsmappe, wählen Sie das Register DATEN und klicken Sie unter der Rubrik Daten abrufen und transformieren auf DATEN ABRUFEN. Wählen Sie im Menü AUS DATEI – AUS EXCEL-ARBEITSMAPPE.

       POWER BI: Klicken Sie auf DATEN ABRUFEN und wählen Sie die Quelle EXCEL.

Sie haben nun die Möglichkeit, eine Excel-Arbeitsmappe auszuwählen. Wählen Sie aus den Übungsdateien zum Buch die Datei 2-01-Umsatz.xlsx. Daraufhin erscheint das Navigator-Fenster, in dem Sie wählen können, welches Arbeitsblatt aus der Excel-Arbeitsmappe eingelesen werden soll. Wählen Sie Umsatz 2015.

Das Navigator-Fenster zeigt Ihnen bei Excel-Quelldateien nicht nur Arbeitsblätter, sondern, wenn verfügbar, auch Tabellen und benannte Bereiche zur Auswahl an.

Bild 2.3Im Navigator-Fenster können Sie das gewünschte Tabellenblatt auswählen.

In der rechten Fensterhälfte sehen Sie eine Vorschau der Tabelle, die importiert wird. Sie können nun bereits auf LADEN klicken, um die Abfrage abzuschließen.

EXCEL:

In Excel haben Sie mehrere Möglichkeiten, das Abfrageergebnis zu laden. Wenn Sie direkt auf die Schaltfläche LADEN klicken, wird eine Tabelle mit den abgerufenen Daten auf einem neuen Arbeitsblatt angelegt. Um weitere Optionen zu erhalten, klicken Sie auf den kleinen Pfeil neben der Schaltfläche und wählen Sie LADEN IN . . .

Bild 2.4Über den Dialog Laden in wird der Zielort bestimmt.

Daraufhin erscheint ein neuer Dialog, in dem Sie mehrere Auswahlmöglichkeiten haben:

       Tabelle: Mit der Standard-Option werden die importierten Daten in einer Tabelle gespeichert. Weiter unten können Sie genau bestimmen, wo die neue Tabelle angelegt werden soll.

       PivotTable-Bericht: Die abgerufenen Daten bilden die Quelle einer Pivot-Tabelle.

       PivotChart: Die abgerufenen Daten dienen als Datenbasis eines Pivot-Charts.

       Nur Verbindung erstellen: Wenn Sie diese Option wählen, werden die importierten Daten nicht in einem Arbeitsblatt angezeigt. Dies kann nützlich sein, wenn Sie mehrere Abfragen kombinieren wollen (vgl. Kapitel 8) oder wenn Sie lediglich Ihr Datenmodell befüllen wollen (siehe nächster Punkt).

       Dem Datenmodell diese Daten hinzufügen: Falls Sie die importierten Daten in einer Pivot-Tabelle oder einem PivotChart verwenden wollen, aktivieren Sie diese Option. Die Daten stehen dann direkt zur Verfügung, ohne dass Sie die Quell-Tabelle angeben müssen. (Diese Option ist nicht in allen Excel-Versionen vorhanden.)

POWER BI:

In Power BI Desktop müssen Sie keinen Zielort für die abgefragten Daten angeben. Sobald Sie auf LADEN klicken, werden sie zu Ihrem Datenmodell hinzugefügt. Anschließend können Sie sie mit anderen Tabellen in Verbindung setzen oder direkt Visualisierungen daraus erstellen.

Um den Bericht schließlich in der Cloud auf Power BI Service verfügbar zu machen, klicken Sie im Register START auf VERÖFFENTLICHEN. Daraufhin können Sie online auf die Visualisierungen zugreifen oder den Bericht mit anderen Nutzern teilen.

Analog zur Option Nur Verbindung erstellen in Excel, haben Sie auch in Power BI Desktop die Möglichkeit, Abfragen so zu erstellen, dass sie nur für andere Abfragen verwendet werden können.

Auf der linken Seite des Abfrage-Editors befindet sich eine Liste mit allen Abfragen einer Datei. Klicken Sie mit der rechten Maustaste auf eine Abfrage und deaktivieren Sie die Option Laden aktivieren. Das bewirkt, dass die Tabelle nicht ins Datenmodell geladen wird.

2.2Aktualisieren der Abfrage

EXCEL:

Wenn Sie den Abfrage-Dialog ganz normal mit LADEN geschlossen haben, erhalten Sie eine einfache Tabelle mit den eingelesenen Daten. Das ist zunächst nicht spektakulär, denn dafür hätten Sie die Daten auch einfach per Copy & Paste in Ihre Arbeitsmappe kopieren können.

Doch nehmen Sie einmal an, die Quelldatei wird regelmäßig geändert. In vielen Firmen gibt es Excel-Listen mit Umsatzzahlen oder Personaldaten, die laufend angepasst werden. Mehrere Personen greifen auf dieselbe Datei zu, um jeweils unterschiedliche Auswertungen zu erstellen. Wer nicht in der Originaldatei arbeiten will oder darf, muss daher immer wieder die Originaldatei öffnen, die aktuellen Daten herauskopieren und in sein Auswertungsmodell übertragen.

Viel schneller geht es mit einer Abfrage, denn dabei müssen Sie nur einmal eine Verbindung zur Quelldatei herstellen. Anschließend können Sie sie jederzeit aktualisieren, um die neuesten Daten zu erhalten. Hierfür klicken Sie im Register DATEN auf ALLE AKTUALISIEREN. Noch schneller geht es mit der Tastenkombination STRG + ALT + F5.

Bild 2.5Aktualisieren Sie alle Abfragen mit einem Klick.

Falls Sie mehrere Abfragen oder Verbindungen in Ihrer Arbeitsmappe haben, möchten Sie vielleicht nur eine bestimmte Abfrage aktualisieren (z. B. um Zeit zu sparen). Auch das ist möglich: Falls am rechten Bildschirmrand nicht die Liste der Abfragen angezeigt wird, aktivieren Sie diese über DATEN – ABFRAGEN UND VERBINDUNGEN. Rechts neben den jeweiligen Abfragen in der Liste können Sie nun auf das Aktualisieren-Symbol klicken. Alternativ können Sie auch einen Rechtsklick auf die Abfrage machen und AKTUALISIEREN wählen.

Bild 2.6Die Liste der Abfragen.

POWER BI:

Auch bei Power BI kann es natürlich vorkommen, dass die Quelldaten sich ändern. Damit die aktuellsten Daten neu geladen werden, klicken Sie einfach auf die Schaltfläche AKTUALISIEREN im START-Register. Dadurch wird die Datenquelle erneut eingelesen. Bei bestimmten Datenquellen haben Sie zusätzlich die Möglichkeit, eine Verbindung per DirectQuery herzustellen. Bild 2.7 zeigt, wie diese Option beim Erstellen einer Abfrage auf eine SQL-Server-Datenbank aussieht.

Bild 2.7DirectQuery beim Erstellen einer SQL-Server-Abfrage.

Wenn Sie eine Abfrage mit DirectQuery erstellen, werden die abgefragten Daten nicht in der Power-BI-Datei gespeichert, wie es normalerweise der Fall wäre. Stattdessen wird eine direkte Verbindung zur Datenquelle hergestellt. Die benötigten Daten werden jedes Mal neu geladen, wenn Visualisierungen erstellt oder verändert werden. Wenn Sie beispielsweise durch Klick auf eine Visualisierung nach einem bestimmten Datenbereich filtern, werden diese Daten live abgerufen. Besonders bei großen Datenmengen kann diese Methode effizienter sein als eine permanente Speicherung der Daten, da immer nur der jeweils benötigte Teil gespeichert wird. Andererseits können jedoch Verzögerungen entstehen, wenn die Verbindung zum Server nicht schnell genug ist.

2.3Abfragen automatisch aktualisieren

EXCEL:

Excel bietet die Möglichkeit, Abfragen automatisch in bestimmten Zeitabständen oder beim Öffnen der Datei zu aktualisieren. Das kann von Vorteil sein, wenn sich die Quelldatei häufig ändert und Sie sicherstellen wollen, immer die neuesten Daten zu haben. Andererseits kann es aber zu störenden Verzögerungen führen. Es hängt also immer vom jeweiligen Anwendungsfall ab, ob eine automatische Aktualisierung sinnvoll ist.

Falls die Liste der Abfragen nicht sichtbar ist, öffnen Sie sie über DATEN – ABFRAGEN UND VERBINDUNGEN. Klicken Sie mit der rechten Maustaste auf eine Abfrage und wählen Sie EIGENSCHAFTEN.

Bild 2.8Die Abfrageeigenschaften.

Bei älteren Excel-Versionen (vor Februar 2018) finden Sie den entsprechenden Dialog unter DATEN – VERBINDUNGEN – EIGENSCHAFTEN.

Im Dialog Abfrageeigenschaften können Sie zum Beispiel einstellen, dass die Abfrage stündlich aktualisiert wird. Oder Sie können sie bei jedem Öffnen der Datei automatisch aktualisieren lassen. Wenn Sie diese Einstellung aktivieren, haben Sie auch die Möglichkeit, die Daten „vor dem Speichern des Arbeitsblatts aus dem externen Datenbereich (zu) entfernen“. Das bedeutet, dass die Tabelle mit den importierten Daten nicht mit der Arbeitsmappe gespeichert wird, sondern immer neu geladen werden muss. Dadurch reduziert sich die Dateigröße, insbesondere bei großen Tabellen. Ein weiterer Vorteil dieser Einstellung ist die Datensicherheit: Bei sensiblen Quelldaten innerhalb eines Firmennetzwerks ist die Datei mit der Abfrage unbrauchbar, solange sich der Benutzer außerhalb des Netzwerks befindet.

POWER BI:

Eine Option zum automatischen Aktualisieren gibt es in Power BI Desktop nicht. Klicken Sie also bei Bedarf auf die Schaltfläche AKTUALISIEREN, um die neuesten Daten einzulesen.

Etwas mehr Flexibilität bietet Power BI Service. Wenn Sie Ihre Daten dort veröffentlichen, können Sie regelmäßige Aktualisierungen einrichten. Die Voraussetzung ist natürlich, dass Ihre Datenquelle auch für Power BI Service zugänglich ist.

Befinden sich Ihre Quelldaten in einer lokalen Datei auf Ihrem Computer und Sie möchten die Abfrage auf Power BI Service mit anderen teilen, ist es das einfachste, die Quelldatei auf OneDrive hochzuladen. Auf diese Weise kann die Abfrage auch online jederzeit aktualisiert werden.

2.4Kann man Abfragen separat speichern?

Sowohl in Excel als auch in Power BI Desktop werden Abfragen zusammen mit der Datei gespeichert. Bei Power BI haben Sie zusätzlich noch die Möglichkeit, die Abfrage zusammen mit dem Datenmodell und dem Bericht in der Cloud zu veröffentlichen.

Vielleicht möchten Sie manchmal aber nur eine Abfrage speichern, unabhängig von anderen Tabellen, Diagrammen und sonstigen Daten. Eine Exportmöglichkeit oder eine Speichern-Schaltfläche im Fenster von Power Query gibt es dafür leider nicht. Aber es gibt ähnliche Alternativen.

EXCEL:

Sie können Excel-Abfragen als ODC-Dateien exportieren. ODC steht für Office Data Connection und ist ein Format, um Verbindungseigenschaften zu speichern. Es basiert auf den Formaten HTML und XML, Sie können es also mit einem beliebigen Texteditor öffnen, wenn Sie neugierig auf den gespeicherten Inhalt sind.

       Um eine Abfrage zu exportieren, öffnen Sie die Seitenleiste Abfragen und Verbindungen in Excel und klicken mit der rechten Maustaste auf eine Abfrage.

       Klicken Sie im Kontextmenü auf den Eintrag Verbindungsdatei exportieren.

       Es erscheint ein Dialogfeld, in dem Sie den Speicherort der Datei auswählen können. Der Standardordner hierfür ist der Ordner Meine Datenquelle in Ihrem Benutzer-Verzeichnis, aber Sie können natürlich auch einen anderen Ordner wählen.

Alternativ zu diesem Kontextmenü-Befehl, gibt es auch eine Schaltfläche VERBINDUNGSDATEI EXPORTIEREN bei den Verbindungseigenschaften auf dem Register Definition.

Diese ODC-Datei enthält nun alle Informationen, um die Abfrage in einer anderen Excel-Datei zu verwenden. Sie können es ausprobieren, indem Sie im Windows Explorer einen Doppelklick auf die Datei machen.

Bild 2.9Erstellen einer Verbindungsdatei über das Kontextmenü der Abfrage.

Wenn Sie bereits eine Arbeitsmappe geöffnet haben, klicken Sie auf dem Register DATEN auf die Schaltfläche VORHANDENE VERBINDUNGEN. Die Verbindungen, die hier aufgelistet sind, stammen aus Ihrem Standard-Verbindungsordner, aber nach einem Klick auf die Schaltfläche NACH WEITEREN ELEMENTEN SUCHEN, können Sie eine beliebige ODC-Datei auswählen.

(Hinweis: In älteren Excel-Versionen finden Sie die Arbeitsmappenverbindungen im Register DATEN unter VERBINDUNGEN.)

POWER BI:

Eine Export-Funktion, die nur die Power-Query-Abfrage betrifft, gibt es in Power BI leider nicht. Eine einfache Alternative ist, den M-Code der Abfrage in einer Textdatei zu speichern und bei Bedarf im Erweiterten Editor einzufügen. In Kapitel 10 erfahren Sie mehr dazu. Eine professionellere Lösung ist das Erstellen eines eigenen Connectors. So werden die verschiedenen Datenquellenverbindungen genannt, die zur Auswahl stehen, wenn Sie auf DATEN ABRUFEN klicken. Es würde zu weit führen, an dieser Stelle zu erklären, wie benutzerdefinierte Connectors erstellt und zertifiziert werden, aber für fortgeschrittene Benutzer ist das durchaus möglich. In Kapitel 15 wird das Grundprinzip kurz gezeigt anhand eines Beispiels mit einer benutzerdefinierten Funktion.

3Transformieren: 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.1Der Power Query-Editor

Um beim Erstellen einer Abfrage zum Abfrage-Editor zu gelangen, klicken Sie im Navigator-Fenster nicht auf LADEN wie im letzten Beispiel, sondern stattdessen auf DATEN TRANSFORMIEREN oder BEARBEITEN.

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 DATEN TRANSFORMIEREN bzw. BEARBEITEN (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.1Der 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.2Transformationsschritte 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.2Mithilfe 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.3Zeilen 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.3Mit 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 MEHR ANZEIGEN. 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 LEERE ENTFERNEN. Alternativ können Sie auch das Häkchen neben dem Eintrag (leer) wegklicken und auf OK 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 START im Bereich Transformieren auf die Schaltfläche ERSTE ZEILE ALS ÜBERSCHRIFTEN VERWENDEN.

       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 OK.

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 ENTER-Taste.

3.5Spalten 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 klicken.

       Machen Sie einen Rechtsklick auf die Spaltenüberschrift und wählen Sie ENTFERNEN. Noch schneller geht es mit der ENTF-Taste auf Ihrer Tastatur.

       Sie können auch gleich mehrere Spalten auf einmal entfernen. Wählen Sie hierzu die Spalten Amount und Price mit gedrückter STRG-Taste aus und entfernen Sie sie.

Unter dem Menüeintrag ENTFERNEN gibt es auch die Option ANDERE SPALTEN ENTFERNEN. Mit diesem Befehl entfernen Sie alle Spalten außer der/den markierten. Dies erspart Klickarbeit, wenn Sie viele Spalten entfernen wollen.

Außerdem kann diese Funktion nützlich sein, wenn Sie nicht sicher sind, dass das Layout der Quelltabelle unverändert bleibt. Bei ENTFERNEN werden nur die ausgewählten Spalten entfernt. Wenn Sie ANDERE SPALTEN ENTFERNEN benutzen, funktioniert die Transformation auch noch, wenn zu einer späteren Aktualisierung zusätzliche Spalten in der Tabelle auftauchen.

3.6Werte ersetzen

Die Quelldatei verwendet offenbar ein fremdes Zahlenformat: Anstatt mit Komma werden Dezimalzahlen mit einem Punkt angegeben. Damit die Zahlen im deutschen System korrekt angezeigt werden, müssen die Punkte durch Kommas ersetzt werden.

       Klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift Umsatz und wählen Sie WERTE ERSETZEN.

       Geben Sie als zu suchenden Wert einen Punkt und als Ersatzwert ein Komma an.

       Bestätigen Sie mit OK.

Bild 3.4Mit Werte ersetzen ersetzen Sie Zeichenfolgen in einer Spalte.

3.7Datentypen bestimmen

In einer ordentlichen Tabelle enthält jede Spalte nur Daten eines bestimmten Typs. Im Abfrage-Editor können Sie daher festlegen, ob eine Spalte z. B. Zahlen, Texte oder Datumswerte enthält.

       In den aktuellsten Versionen von Excel und Power BI sehen Sie neben den Spaltentiteln ein Symbol, das den Datentyp anzeigt. In unserem Beispiel sollte das „ABC“ für den Datentyp Text sein. Klicken Sie auf das ABC-Symbol links neben dem Spaltentitel Umsatz. Falls in Ihrer Version kein Typ-Symbol angezeigt wird, klicken Sie mit der rechten Maustaste auf den Spaltentitel und wählen Sie das Untermenü TYP ÄNDERN.

       Wählen Sie für die Umsatz-Spalte den Typ Dezimalzahl.

Bild 3.5Auswahl des Datentyps.

Power Query versucht bei jeder Abfrage, die Datentypen automatisch zu bestimmen. Dies gelingt in vielen Fällen, doch Sie sollten sich nicht darauf verlassen. Für die Typbestimmung werden nur die ersten 200 Zeilen ausgewertet. Je nachdem, wie eindeutig die ersten 200 Zeilen aussehen und welche Transformationsschritte Sie noch durchführen wollen, kann die automatische Datentypänderung auch kontraproduktiv sein. Um immer auf der sicheren Seite zu sein, empfiehlt es sich daher, die Datentypen immer zu überprüfen und ggf. selbst zu bestimmen.

Das Einstellen des richtigen Datentyps hat mehrere Vorteile:

       Viele Transformationsfunktionen arbeiten nur mit bestimmten Datentypen. Beispielsweise können Berechnungen nur mit Zahlen-Spalten angestellt werden.

       Wie im Filter-Kapitel erwähnt, gibt es Spezialfilter, die nur bei bestimmten Typen möglich sind (Textfilter, Zahlenfilter, Datumsfilter, Zeitfilter, logische Filter).

       Alle Einträge werden entsprechend umgewandelt. Die Erkennungsfunktion ist dabei relativ flexibel. Beispielsweise wird der Text 1. Apr 17 beim Umwandeln in ein Datum richtig erkannt und dargestellt als 01.04.2017 (wobei die Anzeigeart abhängig von der Einstellung des Betriebssystems ist).

       Umwandlungsfehler durch die automatische Datentyperkennung werden vermieden. So werden zum Beispiel bei Zahlen standardmäßig führende Nullen entfernt: Statt 001 wird nur 1 ausgegeben. Es gibt aber Fälle, in denen die führenden Nullen beibehalten werden sollen, z. B. bei Postleitzahlen, Telefonnummern oder Personalnummern. Verwenden Sie in diesen Fällen den Datentyp Text, damit die Nummern so belassen werden, wie sie sind.

In der folgenden Tabelle sehen Sie die verschiedenen Datentypen und ihren Anwendungsbereich.

Tabelle 3.1 Die Datentypen, die für Spalten möglich sind.

Symbol

Bezeichnung

Beschreibung

Beispiele

Dezimalzahl

Dies ist der Standardtyp für Zahlenwerte und kann für ganze Zahlen sowie Dezimalzahlen mit sehr hoher Genauigkeit verwendet werden.

1,5-70,00000152

Feste Dezimalzahl/Währung

Bei diesem Datentyp werden Zahlenwerte mit bis zu vier Nachkommastellen gespeichert. Alle weiteren Nachkommastellen werden speziell gerundet (siehe Hinweiskasten).

23,988712-0,0001

Ganze Zahl

Hiermit speichern Sie ganze Zahlen. Sollten die Zahlen Nachkommastellen haben, werden sie speziell gerundet (siehe Hinweiskasten).

0-154888273

Prozentsatz

Zahlenwerte mit diesem Datentyp werden als Prozentwerte interpretiert. Eine 1 in der Datenquelle entspricht 100 %.

50 %99,99 %

Datum/ Uhrzeit

Zeitwerte mit Datum und Uhrzeit. Es werden alle Datumswerte ab 1900 bis zum Jahr 9999 unterstützt.

31.12.200014:00:01

Datum

Datumswerte ohne Uhrzeit.

06.02.2017

Zeit

Uhrzeit ohne zugehöriges Datum.

06:3011:55

Datum/ Uhrzeit/ Zeitzone

Zusätzlich zum Datum und der Uhrzeit wird noch die Zeitzone angezeigt. Die Zeitzone wird angegeben als der Unterschied zwischen der Ortszeit und der UTC-Zeit (in Deutschland +1:00 zur Winterzeit bzw. +2:00 zur Sommerzeit).Beim Laden der Abfrage werden die Werte jedoch ohne Zeitzone gespeichert und in den Datentyp Datum/Uhrzeit umgewandelt.

21.04.200108:30:00 +01:00

Dauer

Ein Zeitraum, der z. B. bei der Addition oder Subtraktion von Datums- und Zeitwerten ermittelt wird. Er kann daher positiv oder negativ sein. Die Werte werden als Tage, Stunden, Minuten und Sekunden angezeigt, aber als Dezimalzahlen gespeichert. Eine 1 entspricht dabei einem Tag, 1,5 ist ein Tag und 12 Stunden.

1.12:00:00 (= eineinhalb Tage)(= eine 0.00:30:00 halbe Stunde)

Text

Zeichenketten, bestehend aus Buchstaben, Zeichen oder Zahlen. Eine einzelne Zeichenkette darf bis zu 268 Mio. Zeichen enthalten.

Das ist ein Text.'XYZ{$$$}' :-)

TRUE/FALSE

Boolesche Wahrheitswerte, die für verschiedene M-Funktionen von Bedeutung sind, aber auch in Excel- oder DAX-Formeln verwendet werden können.

TRUEFALSE

Binär

Ein einzelner Wert in einer Spalte kann kurioserweise auch eine Datei umfassen. Diese „Werte“ werden in der Vorschau-Tabelle nicht angezeigt, können aber in weiteren Transformationen verwendet werden. Hierfür wird der Datentyp Binär verwendet, der jede Art von Information im Binärformat speichert.

Binary

Any

Spalten vom Typ Any können jede Art von Daten enthalten. Dieser Datentyp wird nach dem Einlesen einer Quelle angezeigt, wenn kein anderer Datentyp eingestellt wurde.Any ist eigentlich kein richtiger Datentyp, denn er kann nicht explizit zugewiesen werden, sondern bedeutet lediglich, dass kein Datentyp festgelegt wurde.

(alles)

Mathematisches Runden: Beim Umwandeln von Datentypen, z. B. von Dezimalzahlen auf ganze Zahlen, verwendet Power Query eine andere Rundungstechnik, als es die meisten Benutzer erwarten würden. So wird die Zahl 2,5 nicht aufgerundet zu 3, sondern abgerundet zu 2. Die Zahl 3,5 wird dagegen aufgerundet zu 4.

Es handelt sich dabei um die sogenannte mathematische Rundung (engl. Round to Even). Die Dezimalstellen 1, 2, 3, 4 werden wie gewohnt abgerundet und die Dezimalstellen 6, 7, 8, 9 werden wie gewohnt aufgerundet. Die 5er-Stellen werden allerdings nicht, wie bei der allgemein üblichen sogenannten kaufmännischen Rundung aufgerundet, sondern so auf- oder abgerundet, dass die gerundete Zahl bzw. Dezimalstelle immer gerade ist.

Beispiele:

       1,1 wird zu 1.

       1,5 wird zu 2.

       1,8 wird zu 2.

       2,4 wird zu 2.

       2,5 wird zu 2.

       2,6 wird zu 3.

       3,5 wird zu 4.

       4,5 wird zu 4.

Diese Art zu runden wird im Alltag zwar eher selten benutzt, hat aber tatsächlich Vorteile. Stellen Sie sich eine große Menge an Dezimalzahlen vor, die zu ganzen Zahlen gerundet werden. Die Zahlen, die auf ,5 enden, liegen genau zwischen zwei ganzen Zahlen. Wenn sie immer aufgerundet werden, entsteht ein leichtes Ungleichgewicht. Der Durchschnitt oder die Summe aus den gerundeten Zahlen wird tendenziell größer sein als bei den ursprünglichen Dezimalzahlen. Beim mathematischen Runden wird davon ausgegangen, dass ungefähr genauso viele gerade wie ungerade Zahlen vorliegen. Wenn das der Fall ist, werden die 5er-Stellen genauso oft auf- wie abgerundet. Diese Rundung erzeugt somit weniger Abweichungen und wird daher auch symmetrische oder unverzerrte Rundung genannt.

Wenn Sie die herkömmliche Rundung verwenden wollen, benutzen Sie die Funktion Number.Round mit dem Rundungsmodus 2 (vgl. Kapitel 14.7.3).

3.8Transformationsschritte prüfen und benennen

Wenn Sie die Übung bis hierher gemacht haben, sehen Sie nun in der Liste auf der rechten Seite alle Transformationen, die Sie vorgenommen haben. Unter Angewendete Schritte können Sie die verschiedenen Schritte anklicken, um in der Vorschau das Ergebnis der jeweiligen Aktion zu sehen.

Achten Sie jedoch darauf, stets wieder den letzten Schritt zu markieren, bevor Sie eine neue Aktion durchführen – es sei denn, Sie möchten einen Schritt vor einem anderen Schritt einfügen.

Bild 3.6Alle Aktionen werden als Schritte aufgezeichnet.

Ihre Aktionen werden automatisch benannt, Sie können sie aber jederzeit umbenennen. Es empfiehlt sich, besonders bei umfangreichen Transformationen, den Schritten aussagekräftige Namen zu geben. Auf diese Weise können Sie später besser nachvollziehen, was genau gemacht wurde.

Bei der Benennung der Transformationsschritte sind Sie relativ frei. Es dürfen nur nicht zwei Schritte den gleichen Namen haben.

       Klicken Sie mit rechts auf den Schritt Gefilterte Zeilen und wählen Sie UMBENENNEN.

       Geben Sie den neuen Namen Kopfzeilen entfernt ein.

       Bestätigen Sie mit der ENTER-Taste.

       Ändern Sie auf die gleiche Weise den Namen des Schritts Gefilterte Zeilen1 in Zwischenüberschriften entfernt.

3.9Abschließen der Transformation

Sie haben nun die Tabelle auf die relevanten Informationen reduziert, die Formate und Datentypen festgelegt sowie die Überschriften definiert. Sie ist nicht nur deutlich übersichtlicher als die Ausgangstabelle, sondern eignet sich nun für Visualisierungen in Power BI bzw. für Berechnungen und Auswertungen in Excel.

       Auf der rechten Fensterseite unter Abfrageeinstellungen sollte noch ein aussagekräftiger Name angegeben werden. Geben Sie den Namen Umsatz November 2017 ein und bestätigen Sie mit der ENTER-Taste.

       Klicken Sie zum Abschließen der Bearbeitung in Excel auf SCHLIESSEN & LADEN bzw. in Power BI auf SCHLIESSEN UND ÜBERNEHMEN. Die Schaltflächen befinden sich jeweils links oben im Register START.

Bild 3.7Hiermit beenden Sie den Editor (links: Excel, rechts: Power BI).

3.10Erneutes Bearbeiten der Abfrage

Die Transformationsschritte sowie die Verbindung zur Quelldatei sind nun in der Abfrage gespeichert. Bei jedem Aktualisieren der Abfrage wird die Quelldatei neu eingelesen und alle Transformationsschritte werden abgearbeitet.

Sie können die Schritte natürlich jederzeit anpassen.

EXCEL:

Sollte am rechten Rand des Excel-Fensters nicht die Liste mit den Arbeitsmappenabfragen angezeigt werden, klicken Sie im Register DATEN auf ABFRAGEN UND VERBINDUNGEN.

In der Liste der Abfragen machen Sie einfach einen Doppelklick auf die Abfrage, um zum Power Query-Editor zu gelangen.

POWER BI:

Im Register START gibt es die Schaltfläche DATEN TRANSFORMIEREN. Damit öffnen Sie die Abfrage im Power- Query-Editor.

4Erweiterte Transformationen

Sowohl Excel als auch Power BI sind leistungsfähige Werkzeuge zum Visualisieren von großen Datenmengen. Erfahrene Nutzer wissen jedoch, dass die Möglichkeiten der Darstellung oft durch die Form bzw. das Layout der Ausgangsdaten beschränkt werden. Besonders wenn Daten bereits als Report aufbereitet sind, kann es schwer sein, eine andere Ansicht zu erstellen.

Glücklicherweise sind Sie mit dem Power Query-Editor gewappnet: Funktionen wie Transponieren, Entpivotieren oder Ausfüllen helfen Ihnen, unübersichtliche Reports zu entwirren und in lesbare Tabellen umzuwandeln.

Werfen wir zunächst einen Blick auf die Beispieldatei 4-01-Arbeitsstunden.xlsx in Excel (Bild 4.1).

Bild 4.1Der Inhalt der Beispieldatei (Ausschnitt).

Die Datei stellt die Nutzungszeiten dreier Maschinen durch verschiedene Mitarbeiter über einen Zeitraum von drei Jahren dar. Die Tabelle ist zwar optisch gut strukturiert, aber sie eignet sich kaum für eine weitere Auswertung. Möchte man beispielsweise die Nutzungszeiten der einzelnen Maschinen für jeden Monat ermitteln, sind erst umständliche Einfügungen und Berechnungen nötig. Eine ansprechende Visualisierung mit Filterfunktion, etwa in Power BI oder mithilfe der Excel Pivot Charts, ist mit dieser Ausgangstabelle nicht möglich: Anstatt einer Monatsspalte gibt es einzelne Spalten für alle Monate, die sich auch noch für jedes Jahr wiederholen (in Bild 4.1 nur teilweise sichtbar). Zudem ist die Zuordnung der Maschinen nur optisch möglich, in vielen Zeilen fehlt die Maschine.

Diese Art von Report ist häufig eine Kopie einer Pivot-Tabelle, wobei die zugrunde liegenden Basisdaten fehlen. Es sind zunächst mehrere Transformierungsschritte nötig, damit die Daten weiter aufbereitet werden können – etwa für einen anderen Report, der die gesamte Nutzungszeit pro Maschine oder pro Jahr zeigt.

Das Ziel der Transformation ist eine Tabelle, wie sie in Bild 4.2 zu sehen ist.

Bild 4.2So soll die Tabelle aussehen, nachdem sie transformiert wurde.

EXCEL:

       In Excel können Sie die Abfrage in derselben Datei wie die Quelldaten erstellen. Öffnen Sie hierzu die Datei 4-01-Arbeitsstunden.xlsx.

       Setzen Sie den Zellzeiger irgendwo in die Liste mit den Arbeitsstunden.

       Klicken Sie im Register DATEN im Bereich Daten abrufen und transformieren auf AUS TABELLE/BEREICH.

       Es erscheint ein Fenster, in dem der Bereich der Tabelle angegeben werden kann. Stellen Sie sicher, dass alle Daten markiert sind.

       Entfernen Sie das Häkchen neben Tabelle hat Überschriften. Die Überschriften werden erst definiert, nachdem die Tabelle im Abfrage-Editor bearbeitet ist.

       Klicken Sie OK, um die Abfrage im Power Query-Editor zu bearbeiten.

Bild 4.3Erstellen Sie eine Tabelle ohne Überschriften.

POWER BI:

       Starten Sie eine neue Abfrage auf die Excel-Datei 4-01-Arbeitsstunden.xlsx.

       Wählen Sie das Tabellenblatt Arbeitsstunden und klicken Sie auf DATEN TRANSFORMIEREN.

4.1Nach unten bzw. oben ausfüllen

Im Abfrage-Editor sehen Sie die Tabelle auf die Rohdaten reduziert, d. h. ohne Formatierungen und Rahmen. Hier wird offensichtlich, dass die optische Zuordnung der Jahre und Maschinen nicht mehr genügt. In der ersten Spalte und der ersten Zeile befinden sich leere Zellen. Diese Zellen müssen mit den entsprechenden Maschinen bzw. Jahren gefüllt werden.

Leere Zellen werden im Abfrage-Editor immer mit dem Hinweistext null angezeigt.

Bild 4.4Die erste Zeile und die erste Spalte enthalten leere Zellen.

       Klicken Sie mit der rechten Maustaste auf den Spaltentitel der ersten Spalte und wählen Sie im Kontextmenü AUSFÜLLEN – NACH UNTEN. Sie finden den Befehl alternativ auch im Register TRANSFORMIEREN.

Bild 4.5Nach dem Ausfüllen stimmen die Zuordnungen der Maschinen.

Durch diese Funktion werden alle leeren Zellen in Spalte1 mit dem jeweils darüberliegenden Wert gefüllt. Maschine A, B und C sind nun nicht mehr nur Überschriften eines Bereichs, sondern können eindeutig den entsprechenden Zeilen zugeordnet werden.

Bei der Zeile mit den Jahren haben wir ein ähnliches Problem: Die Jahreszahlen erscheinen nur einmal, jeweils beim Januar. Leider gibt es die Funktion Ausfüllen nur nach oben und unten, nicht nach rechts oder links. Wir können uns aber mit einem kleinen Trick behelfen.

4.2Zeilen und Spalten vertauschen (Transponieren)

Da es keine Funktion „Ausfüllen – nach rechts“ gibt, müssen wir die Tabelle vorübergehend „kippen“. Bei der Funktion Vertauschen, auch Transponieren genannt, werden Spalten zu Zeilen und umgekehrt. Somit kann die Funktion Ausfüllen – nach unten angewendet werden, bevor die Tabelle wieder zurück „gekippt“ wird.

       Klicken Sie im Register TRANSFORMIEREN auf die Schaltfläche VERTAUSCHEN. Sie befindet sich in der ersten Rubrik Tabelle.

       Wenden Sie nun wie zuvor die Funktion AUSFÜLLEN – NACH UNTEN auf die erste Spalte an.

Nun kann jedem Zahlenwert genau eine Maschine, ein Monat und ein Jahr zugeordnet werden.

4.3Spalten verbinden

Bevor Sie die Vertauschen-Funktion erneut anwenden, sollte ein weiteres Problem aus dem Weg geräumt werden: Eine Tabelle sollte im Idealfall nur eine Zeile als Überschrift haben. Ansonsten funktioniert die Entpivotieren-Funktion später nicht. Wir helfen uns also mit einem weiteren Trick und verbinden die Monats- und Jahresangaben.

       Markieren Sie die ersten beiden Spalten, indem Sie mit gedrückter SHIFT-Taste die Spaltentitel anklicken.

       Klicken Sie mit rechts auf einen der Spaltentitel und wählen Sie SPALTEN ZUSAMMENFÜHREN.

       Es erscheint ein Dialog, in dem Sie das Trennzeichen angeben können. Wählen Sie hier Leerzeichen. Geben Sie als Titel für die neue Spalte Zeitraum ein.

       Klicken Sie auf OK.

Bild 4.6Mit Spalten zusammenführen verbinden Sie zwei Spalten zu einer.

Die Reihenfolge, in der Sie die Spalten markieren, ist übrigens entscheidend für die Reihenfolge, in der sie beim Verbinden angeordnet werden. Also je nachdem, ob Sie zuerst die Jahresspalte oder die Monatsspalte angeklickt haben, ist der erste Eintrag nun „Januar 2015“ oder „2015 Januar“. Das ist in diesem Fall egal, denn die Werte werden später wieder getrennt.