Auswerten mit Power Query
Power Query ist ein Werkzeug, das in Excel bereits eingebaut ist. Damit holen Sie Daten aus dem Cube als einfache Liste, eine Zeile pro Eintrag, wie eine normale Excel-Tabelle. Für die meisten Fragen reicht die Pivot-Tabelle. Power Query lohnt sich, wenn Sie eine Liste weiterverarbeiten oder mit eigenen Excel-Daten kombinieren möchten.
Pivot oder Power Query?
| Pivot-Tabelle | Power Query | |
|---|---|---|
| Ergebnis | Zusammenfassung mit Summen | Liste, eine Zeile pro Eintrag |
| Ideal für | Überblick, Vergleiche, Diagramme | Listen, Weiterverarbeitung, eigene Berechnungen |
| Eigene Excel-Daten dazunehmen | nein | ja |
| Aufwand | einfach | etwas mehr Übung nötig |
Liste aus dem Cube laden
Schritt 1 – Datenquelle wählen
Öffnen Sie eine leere Excel-Datei. Klicken Sie auf Daten › Daten abrufen › Aus Datenbank › Aus SQL Server Analysis Services-Datenbank (Import).
Schritt 2 – Server eingeben
Schritt 3 – Anmelden (nur beim ersten Mal)
Schritt 4 – Cube und Felder wählen
Es öffnet sich das Fenster Navigator.
- Klappen Sie links Ihren Cube auf, z. B. SpitexLeistungsCube.
- Setzen Sie Häkchen bei den gewünschten Feldern: Einsatzgrund, Klient & Zeit (Jahr)
- Rechts sehen Sie sofort eine Vorschau.
Schritt 5 – Laden
- Laden: Die Liste erscheint sofort als Excel-Tabelle auf einem neuen Blatt.
- Daten transformieren: Sie können die Liste vorher noch anpassen
- Ihre Liste steht als normale Excel-Tabelle. Sie können sie sortieren, filtern und Formeln daneben schreiben
- Nicht zu viele Felder auf einmal: Jedes zusätzliche Feld macht die Liste länger. Mit Feldern wie Datum oder Bewohner kann sie schnell sehr lang werden. Beginnen Sie mit wenigen Feldern und filtern Sie früh.
Liste anpassen im Power-Query-Editor
Im Power-Query-Editor bereiten Sie die Liste auf, bevor sie in Excel landet. So öffnen Sie den Editor:
- beim Laden: im Navigator auf Daten transformieren klicken, oder
- später: Daten › Abfragen und Verbindungen und dann rechts einen Doppelklick auf die Abfrage
Was Sie hier tun können
Im Power-Query-Editor bereiten Sie Ihre Liste auf, bevor sie in Excel landet. Sie können nichts kaputt machen. Ihre Daten im Cube bleiben immer unverändert, Sie bearbeiten nur Ihre eigene Kopie.
So öffnen Sie den Editor:
- beim Laden: im Navigator auf Daten transformieren klicken
- später: Daten › Abfragen und Verbindungen und dann rechts einen Doppelklick auf die Abfrage
So ist der Editor aufgebaut
| Bereich | Wo? | Wofür |
|---|---|---|
| Menüband | oben | alle Befehle, verteilt auf Register (siehe unten) |
| Abfragen | links | Ihre Abfragen, z. B. «SpitexLeistungsCube» |
| Vorschau | Mitte | die ersten Zeilen Ihrer Liste. Jede Spalte hat oben einen Kopf mit Pfeil ▼. |
| Angewendete Schritte | rechts | alles, was Sie bisher getan haben, Schritt für Schritt |
| Formelleiste | über der Vorschau | zeigt den Befehl des gewählten Schritts. Sie brauchen sie nicht. |
Spaltennamen lesen: Mitarbeiter.Mitarbeiter Nr bedeutet: Feld Mitarbeiter Nr aus der Dimension Mitarbeiter. Das Symbol vor dem Namen zeigt die Art: ABC = Text, 123 = Zahl.
Die Register im Menüband
| Register | Wofür | Brauchen Sie es? |
|---|---|---|
| Start | Schließen & laden, Spalten entfernen, Zeilen behalten | oft |
| Transformieren | eine bestehende Spalte verändern | oft |
| Spalte hinzufügen | eine neue Spalte berechnen | manchmal |
| Ansicht | Anzeige-Einstellungen | selten |
| Cubetools › Verwalten | weitere Felder oder Kennzahlen aus dem Cube holen | oft |
Die wichtigsten Handgriffe
Die Beispiele zeigen den Cube Spitex Leistungen.
Weitere Felder aus dem Cube holen
Ein weiteres Feld als Spalte, z. B. Kunde: Cubetools › Verwalten › Elemente hinzufügen › Feld anhaken › OK
Aufräumen
| Ich möchte … | So geht's |
|---|---|
| eine Spalte umbenennen, z. B. «Mitarbeiter.Mitarbeiter Nr» → «Personal-Nr.» | Doppelklick auf den Spaltenkopf und den neuen Namen eintippen |
| eine Spalte entfernen | Rechtsklick auf den Spaltenkopf › Entfernen |
| nur bestimmte Spalten behalten | Gewünschte Spalten mit Strg + Klick markieren › Rechtsklick › Andere Spalten entfernen |
| die Reihenfolge ändern | Spaltenkopf mit der Maus nach links oder rechts ziehen |
| Text ersetzen, z. B. «(leer)» durch «unbekannt» | Spalte markieren › Transformieren › Werte ersetzen |
Filtern und sortieren
| Ich möchte … | So geht's |
|---|---|
| nur bestimmte Einträge, z. B. nur einen Anstellungstyp | Pfeil ▼ im Spaltenkopf › Einträge anhaken › OK |
| leere Zeilen entfernen | Pfeil ▼ › Häkchen bei (null) entfernen |
| sortieren, z. B. nach Dauer absteigend | Pfeil ▼ › Absteigend sortieren |
Rechnen
| Ich möchte … | So geht's |
|---|---|
| Minuten in Stunden umrechnen | Spalte Dauer Min markieren › Spalte hinzufügen › Standard › Dividieren › 60 › OK. Danach die neue Spalte in «Dauer Std» umbenennen. |
| zusammenfassen, z. B. Summe pro Mitarbeiter | Transformieren › Gruppieren nach › Gruppieren nach Mitarbeiter.Mitarbeiter › Neuer Spaltenname «Total Minuten» › Vorgang Summe › Spalte Dauer Min › OK |
| eine eigene Berechnung, z. B. Minuten pro Einsatz | Spalte hinzufügen › Benutzerdefinierte Spalte › Formel [Dauer Min] / [Anzahl Einsätze] › OK |
| Dezimalstellen runden | Spalte markieren › Transformieren › Runden |
Datentyp – wenn Zahlen als Text erkannt werden
- Steht vor einer Spalte ABC, obwohl darin Zahlen stehen, kann Excel damit nicht rechnen.
- Klicken Sie auf das Symbol ABC im Spaltenkopf und wählen Sie Ganze Zahl oder Dezimalzahl.
- Monate sortieren: Die Spalte Zeit.Monat ist Text. Sie wird deshalb alphabetisch sortiert (April, August, Dezember …). Möchten Sie die Monate in der richtigen Reihenfolge, holen Sie zusätzlich ein Datumsfeld über Elemente hinzufügen und sortieren danach.
Angewendete Schritte – Ihr Protokoll
Power Query schreibt rechts unter Angewendete Schritte jeden Schritt mit. Das hat drei Vorteile:
| Automatisch wiederholen | Beim Aktualisieren laufen alle Schritte von selbst wieder ab, mit den neuen Daten. |
| Zurückschauen | Klicken Sie auf einen Schritt, um die Liste so zu sehen, wie sie zu diesem Zeitpunkt war. |
| Rückgängig machen | Klicken Sie auf das ✕ neben einem Schritt, um ihn zu löschen. |
Fertig – Liste nach Excel holen
Klicken Sie auf Start › Schliessen & laden. Die Liste erscheint als Excel-Tabelle auf einem neuen Blatt. Lieber als Pivot-Tabelle? Wählen Sie Start › Schließen & laden ▾ › Schließen & laden in … › PivotTable-Bericht. So können Sie Ihre aufbereitete Liste wie in Kapitel 3 auswerten.
Mit eigenen Daten kombinieren
Ein Beispiel: Sie haben eine eigene Excel-Tabelle mit Zielwerten pro Haus. Daneben möchten Sie die tatsächlichen Stürze aus dem Cube sehen und die Abweichung berechnen.
| Haus | Zielwert Stürze |
|---|---|
| Haus Ahorn | 8 |
| Haus Birke | 6 |
Schritt 1 – Liste aus dem Cube holen
Schritt 2 – Ihre eigene Tabelle holen
Klicken Sie in Ihre Zielwert-Tabelle und dann auf Daten › Aus Tabelle/Bereich. Klicken Sie im Editor oben auf den Pfeil bei Schließen & laden und wählen Sie Schließen & laden in … › Nur Verbindung erstellen. Nennen Sie die Abfrage z. B. «Zielwerte».
Schritt 3 – Zusammenführen
- Klicken Sie auf Daten › Daten abrufen › Abfragen kombinieren › Zusammenführen.
- Wählen Sie oben Stürze aus Cube und unten Zielwerte.
- Klicken Sie in beiden Tabellen auf die Spalte Haus.
- Wählen Sie bei Join-Art den Eintrag Linker äußerer und klicken Sie auf OK.
Schritt 4 – Zielwerte einblenden
In der Liste gibt es jetzt eine neue Spalte «Zielwerte». Klicken Sie in deren Kopf auf das Symbol mit den zwei Pfeilen, setzen Sie ein Häkchen bei Zielwert Stürze und klicken Sie auf OK.
Schritt 5 – Abweichung berechnen (optional)
- Klicken Sie auf Spalte hinzufügen › Benutzerdefinierte Spalte.
- Geben Sie als Name Abweichung ein.
- Formel:
[Anzahl] - [Zielwert Stürze]› OK.
Schritt 6 – Laden
- Start › Schliessen & laden
- Die Namen müssen genau gleich geschrieben sein. «Haus Ahorn» und «Haus Ahorn» (mit zwei Leerzeichen) gelten als verschieden. Bleiben Zielwerte leer, prüfen Sie die Schreibweise in Ihrer eigenen Tabelle. Am sichersten kopieren Sie die Namen aus der Cube-Liste.
Aktualisieren und Probleme lösen
Neue Daten holen
Klicken Sie auf Daten › Alle aktualisieren (oder Strg + Alt + F5).
Power Query holt die neuesten Daten aus dem Cube und übernimmt Änderungen in Ihren eigenen Tabellen. Alle Schritte aus 4.3 und 4.4 laufen automatisch mit.
Automatisch beim Öffnen aktualisieren
- Klicken Sie auf Daten › Abfragen und Verbindungen.
- Rechtsklick auf die Abfrage › Eigenschaften …
- Setzen Sie ein Häkchen bei Daten beim Öffnen der Datei aktualisieren und klicken Sie auf OK.
- Speichern Sie die Datei.
Kennwort geändert (nur Webfarm-Kunden)
- Klicken Sie auf Daten › Daten abrufen › Datenquelleneinstellungen …
- Wählen Sie den NexusCubes-Server › Berechtigungen bearbeiten …
- Klicken Sie bei Anmeldeinformationen auf Bearbeiten …, geben Sie das neue Kennwort ein und klicken Sie auf Speichern.
💡 Sie können dort auch Berechtigungen löschen wählen. Dann fragt Excel beim nächsten Aktualisieren neu nach Benutzer und Kennwort.