Power Query

class: title-slide center middle

#  Power Query
## Excel-Schulung Studierendenwerk Bielefeld
### Jens Klenke, M.Sc.

---
class: top, left
## Power Query &mdash; Übersicht

- Einleitung
- Datenquelle
- Datenaufbereitung
- Daten kombinieren
- Erweiterte Funktionen 
- Abschluss

---
class: inverse middle center

# Einleitung

---

## Was ist Power Query?

**Definition:**

Ein Tool zum **Importieren, Transformieren und Bereinigen** von Daten

**Wichtigster Unterschied zu normalen Excel-Formeln:**

- Formeln arbeiten mit **einzelnen Zellen** (`=A1*B1`)
- Power Query arbeitet mit **ganzen Tabellen** in nachvollziehbaren **Schritten**

**Typische Anwendungsfälle:**

- Wiederkehrende Analysen
- Datenaufbereitung

---

## Power Query

**Menüpfad:**

.blockquote.exercise[Daten → Daten abrufen und transformieren]

**Wichtigsten Funktionen:**

.font90[

| Button | Funktion |
|--------|----------|
| Daten abrufen | Neue Datenquelle importieren |
| Vorhandene Verbindungen | Bereits erstellte Abfragen erneut öffnen |
| Alle aktualisieren | Alle Abfragen neu berechnen |

]

**Bereits vorhandene Tabelle nutzen:**

.blockquote.exercise[Tabelle markieren → Daten → Aus Tabelle/Bereich]

---

## Power Query-Editor

**Öffnet sich automatisch** nach dem Import einer Datenquelle

**Aufbau des Editors:**

.font90[

| Bereich | Inhalt |
|---------|--------|
| **Vorschau (Mitte)** | Zeigt die Daten, wie sie aktuell aussehen |
| **Abfragen (links)** | Liste aller vorhandenen Abfragen |
| **Einstellungen (rechts)** | Name der Abfrage + **Angewandte Schritte** |

]

- Änderungen im Editor wirken sich **nicht direkt** auf die Daten in Excel selbst aus
- Ausgabe in einem neuen Spreadsheet

---

## Angewandte Schritte

Jede Aktion wird als **einzelner** Schritt aufgezeichnet

.font90[

**Beispiel:**

```
1. Quelle
2. Geänderter Typ
3. Entfernte Spalten
4. Gefilterte Zeilen
5. Duplikate entfernt
```

]

.font90[

**Vorteile:**

- Jeder Schritt ist **einzeln anklickbar** → Vorschau zu jedem Zeitpunkt sichtbar
- Schritte können **nachträglich bearbeitet, gelöscht oder umsortiert** werden
- Komplizierte **Datenaufbereitung** einfacher möglich
- Bei neuen Rohdaten: Alle Schritte werden **automatisch erneut durchlaufen** (Klick auf "Aktualisieren")

]

---

## Vergleich

**Power Query vs. normale Excel-Formeln**

.font90[

| Aspekt | Excel-Formeln | Power Query |
|--------|----------------|--------------|
| Arbeitet mit | Einzelnen Zellen | Ganzen Tabellen |
| Nachvollziehbarkeit | Formel muss man selbst "lesen" | Schritte sind **protokolliert & benannt** |
| Automatisierung bei neuen Daten | Formeln müssen oft neu gezogen werden | **Ein Klick auf "Aktualisieren"** reicht |
| Rückgängig machen | Nur über Strg+Z (begrenzt) | Jeder Schritt einzeln **löschbar** |
| Ziel | Berechnung/Ergebnis in der Zelle | **Saubere, transformierte Datentabelle** |

]

---
class: inverse middle center

# Datenquelle

---

## Daten importieren: Die wichtigsten Quellen

**Menüpfad:**

.blockquote.exercise[Daten → Daten abrufen → Quelle wählen]

.font90[

**Die gängigsten Quellen im Überblick:**

| Quelle | Anwendungsfall |
|--------|-----------------|
| **Aus Tabelle/Bereich** | Bereits vorhandene Excel-Tabelle nutzen |
| **Aus Text/CSV** | Einzelne Rohdaten-Datei importieren |
| **Aus Arbeitsmappe** | Daten aus einer anderen Excel-Datei |
| **Aus Ordner** | Mehrere Dateien gleichzeitig kombinieren |
| **Aus dem Web** | Tabelle direkt von einer Webseite laden |

]

---

## Import aus Text/CSV-Datei

**Menüpfad:** .blockquote.exercise[Daten → Daten abrufen → Aus Datei → Aus Text/CSV]

**Wichtige Einstellungen im Importfenster:**

| Option | Bedeutung |
|--------|-----------|
| **Dateiursprung** | Zeichensatz |
| **Trennzeichen** | Komma, Semikolon, Tab, etc. |
| **Datentyp erkennen** | Automatisch oder manuell festlegen |

**Hinweis:** Bei falsch dargestellten Umlauten (`Ã¤` statt `ä`) meist am **Dateiursprung** liegen

- Auf "Unicode (UTF-8)" umstellen!

---

## Import aus Ordner: Mehrere Dateien kombinieren

**Menüpfad:** .blockquote.exercise[Daten → Daten abrufen → Aus Datei → Aus Ordner]

**Typischer Anwendungsfall:**

- Monatsberichte zusammenfassen

**Ablauf:**
```
1. Ordner auswählen
2. Alle Dateien werden aufgelistet
3. "Kombinieren und transformieren" wählen
4. Excel erkennt automatisch das gemeinsame Format
```

Vorteil: 
- Neue Datei im Ordner → Einfach _Aktualisieren_ klicken

---

## Import aus einer anderen Excel-Arbeitsmappe

**Menüpfad:** .blockquote.exercise[Daten → Daten abrufen → Aus Datei → Aus Excel-Arbeitsmappe]

**Wichtig:**

- Es öffnet sich eine **Navigator-Ansicht** mit allen Tabellenblättern/Tabellen der ausgewählten Datei
- Man kann **mehrere** Blätter/Tabellen gleichzeitig auswählen

**Vorteile:**

Die Verbindung bleibt **aktiv**
- Änderungen können durch _Aktualisieren_ **automatisch** übernehmen

---
class: inverse middle center

# Datenaufbereitung

---

## Spalten entfernen & auswählen

**Spalten entfernen:** .blockquote.exercise[Spalte(n) markieren → Rechtsklick → Spalten entfernen]

**Andere Spalten entfernen:** .blockquote.exercise[Rechtsklick → Andere Spalten entfernen]

**Praktischer Unterschied:**

| Option | Wirkung |
|--------|---------|
| Spalten entfernen | Nur die markierten Spalten verschwinden |
| **Andere** Spalten entfernen | Nur die markierten Spalten **bleiben** übrig |

---

## Zeilen filtern

**Über den Spaltenfilter:** .blockquote.exercise[Klick auf den Dropdown-Pfeil in der Spaltenüberschrift]

**Wichtige Filtermöglichkeiten:**

- Textfilter (enthält, beginnt mit, ...)
- Zahlenfilter (größer als, zwischen, ...)
- Leere/Nicht-leere Werte filtern

**Kopf-/Fußzeilen entfernen:** .blockquote.exercise[Start → Zeilen entfernen → Erste Zeile entfernen / Unterste Zeilen entfernen]

- *Bspw. Rohdaten entfernen*

---

## Datentypen ändern

Power Query erkennt Datentypen oft **automatisch**, aber nicht immer korrekt

**Ändern:** .blockquote.exercise[Klick auf Symbol vor Spaltenname → gewünschten Typ wählen]

**Wichtigste Datentypen:**

```
Text
Ganze Zahl
Dezimalzahl
Datum
Datum/Uhrzeit
Wahr/Falsch
```

.font90[

**Hinweis:** Falsche Datentypen sind  eine der **häufigsten Fehlerquellen**]

---

## Duplikate entfernen

**Menüpfad:** .blockquote.exercise[Spalte(n) markieren → Start → Zeilen entfernen → Duplikate entfernen]

**Wichtig zu wissen:**

- Bei **einer** markierten Spalte: Duplikate werden nur anhand dieser Spalte erkannt
- Bei **mehreren** markierten Spalten: Eine Zeile gilt nur als Duplikat, wenn **alle** markierten Spalten übereinstimmen

**Praxisbeispiel:**
```
Markiert: nur "Name"        → "Max Müller" käme nur einmal vor
Markiert: "Name" + "Datum"  → "Max Müller" darf an unterschiedlichen Tagen mehrfach vorkommen
```

---

## Text bereinigen: Leerzeichen & Sonderzeichen

.font90[

**Menüpfad:** .blockquote.exercise[Spalte markieren → Transformieren → Format]
]

.font90[

| Funktion | Wirkung |
|----------|---------|
| **Kürzen** | Entfernt Leerzeichen am Anfang/Ende |
| **Bereinigen** | Entfernt nicht druckbare Zeichen (z.B. Zeilenumbrüche) |
| **Groß-/Kleinschreibung** | Vereinheitlicht Schreibweise (GROSS, klein, Erster Buchstabe groß) |

]

---

## Werte ersetzen

**Menüpfad:** .blockquote.exercise[Spalte markieren → Start → Werte ersetzen]

.font90[

**Dialogfenster:**

| Feld | Eingabe |
|------|---------|
| Zu suchender Wert | z.B. `" km"` |
| Ersetzen durch | *(leer lassen)* |

- Zur Datenbereinigung wird das Feld leer gelassen

]

.font90[

**Hinweise:**
- Groß-/Kleinschreibung vereinheitlichen, um die Anzahl der Fälle zu verkleinern
- Der Datentyp kann erst anschließend geändert werden

]

---

## Spalten teilen

**Menüpfad:** .blockquote.exercise[Spalte markieren → Transformieren → Spalte teilen]

.font90[

**Wichtigste Optionen:**

| Kriterium | Beispiel |
|-----------|----------|
| Nach Trennzeichen | Komma, Leerzeichen, Semikolon |
| Nach Anzahl Zeichen | z.B. immer nach 3 Zeichen |
| Nach Position | An fester Stelle trennen |

]

.font90[

**Beispiel (nach Trennzeichen " "):**

| Vorher | Nachher |
|--------|---------|
| "Max Müller" | "Max"  "Müller" |
| "Anna Schmidt" | "Anna"  "Schmidt" |

]

---

## Spalten zusammenführen

**Menüpfad:** .blockquote.exercise[Mehrere Spalten markieren → Transformieren → Spalten zusammenführen]

.font90[

**Optionen:**

- Trennzeichen zwischen den Werten
- Neuer Variablenname

]

.font90[

**Beispiel:**

| Vorname | Nachname | → Zusammengeführt |
|---------|----------|---------------------|
| Max | Müller | "Max Müller" |
| Anna | Schmidt | "Anna Schmidt" |

]

---

## Pivotieren vs. Entpivotieren

### Grundidee

**Zwei grundlegende Tabellenformate:**

.font90[

| Format | Beschreibung |
|--------|--------------|
| **Breit (Wide)** | Jede Kategorie hat ihre **eigene Spalte** |
| **Lang (Long)** | Jede Kombination steht in einer **eigenen Zeile** |

]

.font90[

**Funktionen:**

> **Pivotieren** = Lang → Breit (Zeilen werden zu Spalten)

> **Entpivotieren** = Breit → Lang (Spalten werden zu Zeilen)

]

???

Wieso brauchen wir das?

---

## Entpivotieren: Breit → Lang

**Menüpfad:** .blockquote.exercise[Spalte markieren → Transformieren → Spalten entpivotieren]

.font90.pull-left[

**Beispiel**

| Stadt | Jan | Feb | Mär |
|-------|-----|-----|-----|
| Bielefeld | 100 | 120 | 90 |
| Essen | 50 | 60 | 55 |

]

.font90.pull-right[

**Ergebnis:**

| Stadt | Attribut | Wert |
|-------|----------|------|
| Bielefeld | Jan | 100 |
| Bielefeld | Feb | 120 |
| Bielefeld | Mär | 90 |
| Essen | Jan | 50 |
| Essen | Feb | 60 |
| Essen | Mär | 55 |

]

.font90[

- **Spaltennamen** & **-typen** ggf. nach dem Entpivotieren anpassen
]

---

## Pivotieren: Lang → Breit

**Menüpfad:** .blockquote.exercise[Spalte markieren → Transformieren → Spalte pivotieren]

.font90[

**Wichtige Einstellungen im Dialogfenster:**

| Feld | Bedeutung |
|------|-----------|
| **Werte-Spalte** | Enthält die Zahlen, die verteilt werden sollen |
| **Aggregatfunktion** | Falls mehrere Werte zusammenfallen (Summe, Anzahl, ...) |

]

- Breites Format führ eine übersichtlichere Darstellung

---

## Gruppieren nach

Datenaggregration in Power Query

**Menüpfad:** .blockquote.exercise[Start → Gruppieren nach]

.font90[

**Dialogfenster**

| Einstellung | Bedeutung |
|-------------|-----------|
| Gruppieren nach | Spalte(n), nach denen zusammengefasst wird |
| Neue Spaltenname | Name der Ergebnisspalte |
| Vorgang | Summe, Anzahl, Durchschnitt, Max, Min, ... |

]

.font90[

- Ähnlich wie bei **PivotTable**, aber nicht dynamisch
- Gruppierung über mehrere Spalten möglich (`Weitere` im Dialogfenster)

]

---

class: inverse middle center

# Daten kombinieren

---

## Daten kombinieren

**Zentrale Frage:**

> Daten **untereinander** oder **nebeneinander** kombinieren?

.font90[

| Methode | Wirkung | Vergleichbar mit |
|---------|---------|-------------------|
| **Anfügen** | Tabellen werden **untereinander** gehängt | UNION (SQL) |
| **Zusammenführen** | Tabellen werden **nebeneinander** verknüpft | SVERWEIS / JOIN (SQL) |

]

---

## Anfügen

**Menüpfad:** .blockquote.exercise[Start → Abfragen kombinieren → Anfügen]

**Voraussetzung:** Beide Tabellen haben die **gleichen Spaltennamen**

**Beispiel:**

.font90.pull-left[

*Januar*:

| Stadt | Umsatz |
|-------|--------|
| Bielefeld | 100 |

]

.font90.pull-right[

*Februar*:

| Stadt | Umsatz |
|-------|--------|
| Bielefeld | 120 |

]

.font90[

**Ergebnis:**

| Stadt | Umsatz |
|-------|--------|
| Bielefeld | 100 |
| Bielefeld | 120 |

]

---

## Zusammenführen

**Menüpfad:** .blockquote.exercise[Start → Abfragen kombinieren → Zusammenführen]

**Voraussetzung:** Beide Tabellen haben eine **gemeinsame Schlüsselspalte** (z.B. ID, Name)

**Beispiel:**

.font90.pull-left[

*Städte*:

| Stadt | Bundesland |
|-------|-----------|
| Bielefeld | NRW |

]

.font90.pull-right[

*Umsatz*:

| Stadt | Umsatz |
|-------|--------|
| Bielefeld | 100 |

]

.font90[

**Ergebnis:**

| Stadt | Bundesland | Umsatz |
|-------|-----------|--------|
| Bielefeld | NRW | 100 |

]

---

## Zusammenführen

**JOIN-Arten**

- Welche Daten sollen wie zusammengeführt werden

.font90[

**Dialogfenster:**

| Verknüpfungsart | Wirkung |
|-------------------|---------|
| **Left Outer** (Standard) | Alle Zeilen aus Tabelle 1, passende aus Tabelle 2 |
| **Right Outer** | Alle Zeilen aus Tabelle 2, passende aus Tabelle 1 |
| **Full Outer** | Alle Zeilen aus **beiden** Tabellen |
| **Inner** | Nur Zeilen, die in **beiden** Tabellen vorkommen |
| **Anti (Left/Right)** | Nur Zeilen, die **nicht** übereinstimmen |

]

---

## Zusammenführen

.font80[ ]

**Zwischenschritt:**

Nach dem Zusammenführen  nur eine neue Spalte mit **verschachtelten Tabellen**

.font80[ ]

**Erweitern:** .blockquote.exercise[Klick auf das Symbol (⇔) in der Spaltenüberschrift]

- Aushwahl, **welche Spalten** übernommen werden sollen

---
class: inverse middle center

# Erweiterte Funktionen

---

## Benutzerdefinierte Spalten

**Menüpfad:** .blockquote.exercise[Spalte hinzufügen → Benutzerdefinierte Spalte]

**Eigene Berechnung mit der _M_-Sprache:**

```
= [Menge] * [Preis]
```

> Vergleicbar zu Excel-Formeln, aber mit eckigen Klammern für Spaltennamen

**Wichtig:**

Power Query nutzt eine **eigene Sprache** namens **M**, die Syntax ist etwas anders als bei Excel-Formeln, das Grundprinzip aber ähnlich

---

## Bedingte Spalten

**Menüpfad:** .blockquote.exercise[Spalte hinzufügen → Bedingte Spalte]

.font90[

Gleiche Funktion wie `WENN`

]

.font80[
**Dialogfenster:**

| Einstellung | Bedeutung |
|-------------|-----------|
| **Neuer Spaltenname** | Name der neu zu erstellenden Spalte |
| **Spaltenname** | Spalte, die geprüft werden soll |
| **Operator** | Vergleichsart (gleich, größer als, kleiner als, enthält, ...) |
| **Wert** | Vergleichswert (fest eingegeben oder aus anderer Spalte gewählt) |
| **Ausgabe** | Ergebnis, falls die Bedingung zutrifft ("Dann") |
| **Regel hinzufügen** | Weitere Bedingung ergänzen (wie "Sonst wenn") |
| **Sonst** (Otherwise) | Ergebnis, falls **keine** Bedingung zutrifft |

]
--

.font90[

**Vorteil:** Kein Formel-Tippen, alles in einem Schritt und wiederholbar
]

---
class: inverse middle center

# Abschluss

---

## Ergebnis laden

**Menüpfad:** .blockquote.exercise[Start → Schließen & laden → Schließen & laden in...]

.font90[

**Wichtigste Lade-Optionen:**

| Option | Wirkung |
|--------|---------|
| **Tabelle** | Ergebnis wird als normale Excel-Tabelle eingefügt |
| **PivotTable-Bericht** | Direkt als Grundlage für eine PivotTable |
| **Nur Verbindung erstellen** | Daten werden **nicht** in ein Tabellenblatt geladen, nur intern gespeichert (z.B. als Zwischenschritt für weitere Abfragen) |

]

.font90[

**Tipp:** _Nur Verbindung erstellen_ besonders hilfreich, wenn die Abfrage zur Vobereitung für weitere Abfragen dient

]

---

## Aktualisieren &mdash; Vorteil von Power Query

**Menüpfad:** .blockquote.exercise[Daten → Alle aktualisieren]

- Alle **angewandten Schritte** werden automatisch erneut durchgeführt mit der **neuen** Datenbasis

.font90[

**Automatische Aktualisierung**

**Menüpfad:** .blockquote.exercise[Rechtsklick auf Abfrage → Eigenschaften]

]

.font90[

**Optionen:**

| Option | Wirkung |
|--------|---------|
| Bei Öffnen der Datei aktualisieren | Abfrage läuft automatisch beim Öffnen |
| Aktualisieren alle X Minuten | Für regelmäßig wechselnde Datenquellen |
| Im Hintergrund aktualisieren | Excel bleibt währenddessen bedienbar |

]

---

## Abfrage nachträglich bearbeiten

**Menüpfad:** .blockquote.exercise[Daten → Abfragen anzeigen → Doppelklick auf Abfrage]

**Der Power Query-Editor öffnet sich erneut** mit allen bisherigen Schritten

- Einzelne Schritte können bearbeitet, gelöscht oder neu hinzugefügt werden
- Änderungen wirken sich erst nach "Schließen & laden" auf die Excel-Tabelle aus