Tipps & Tricks in Power Query

    Im heutigen Artikel haben wir Tipps und Tricks aus unserem Alltag mit Power Query zusammengefasst. Es geht um das Erstellen von Dimensions-Tabellen aus flachen Strukturen. Außerdem um den richtigen Umgang mit Verweisen und die Organisation mit Gruppen. Und warum man manchmal das Laden von Abfragen deaktivieren möchte.

    Verweise & Duplikate

    Legen Sie in Power Query eine neue Abfrage an. Im Editor sehen Ihre Daten in einer Vorschau. Dort in der linken Hälfte finden sind Ihre neue und gegebenenfalls andere Abfragen in einer Liste dargestellt.

    Power Query Editor

    Sie können jede Abfrage duplizieren oder einen Verweis darauf erstellen.

    Duplizieren und Verweis

    Die Funktion Duplizieren kopiert die Abfrage. Die kopierte Abfrage ist damit unabhängig vom Original. Für einen Test sehr nützlich.

    Erstellen Sie hingegen einen Verweis, wird eine zweite Abfrage angelegt. Diese neue Abfrage greift auf das Ergebnis der ersten zu. Die M-Syntax der zweiten Abfrage ist denkbar einfach.

    M-Syntax eines Verweis

    Wozu benutzt man Verweise?

    Daten liegt häufig in einer großen zweidimensionalen Form vor. Beispielsweise bei einer Tabelle Auftragspositionen.

    Zweidimensionale Tabelle mit Autragsdaten

    Dabei wären uns eine eigene Materialtabelle, eine Auftragspositionstabelle und eine Kundentabelle lieber. Mit diesen Tabellen (und sicherlich noch einer Kalender-Tabelle) können wir ein Star-Schema aufbauen. In diesen separierten Tabellen dienen die Felder Materialnr und Kundennr als Verknüpfung.

    Leider kann man oftmals an der Datenquelle nichts ändern. Trotzdem können wir uns mit Power Query behelfen. Als erstes nehmen wir uns die die Materialtabelle vor. Folgende Schritte führen wir nacheinander aus:

    • Verweise auf die Auftragspositionen-Tabelle
    • umbenennen der neuen Abfrage in DimMaterial
    • Funktion Spalten auswählen und nur die Spalten Materialnr und Materialtext behalten.
    • Spalte Materialnr markieren und Funktion Zeilen entfernen – Duplikate entfernen auswählen

    Fertig ist die Dimensionstabelle DimMaterial.

    DimMaterial als Ergebnis der Transformation

    Und Für die Kundendaten gehen Sie gleich vor. Damit bleibt am Ende folgende Tabelle übrig:

    DimKunden als Ergebnis der Transformation

    Im letzten Schritt bearbeiten wir die Faktentabelle. Die Felder Materialtext oder Kundenort benötigen wir dort nicht mehr. Diese Spalten dürfen wir in der Ausgangstabelle aber nicht löschen! Die Abfragen DimMaterial und DimKunden verweisen auf diese Spalten. Daher erstellen wir einen weiteren Verweis auf die Tabelle Auftragspositionen und nennen diese FaktAuftragspositionen. Über die Funktion Spalten wählen aktivieren wir noch folgende Spalten:

    FaktMaterial als Ergebnis der Transformation

    Diese drei Tabellen können wir später im Datenmodell über die Felder Kundennr und Materialnr wieder in Beziehung setzen.

    Abfragen nicht laden

    Vielleicht haben Sie im Kontextmenü einer Abfrage in Power BI die Option Laden aktivieren gefunden. Deaktivieren Sie diese Option, wird die Abfrage kursiv angezeigt. Damit wird die Abfrage nicht mehr geladen und die Ergebnisse der Abfrage stehen im Bericht nicht mehr zur Verfügung.

    Laden deaktivieren in PowerBI

    In Excel Power Pivot ist das ein kleines bisschen umständlicher. Im Dialog Laden In definieren Sie, ob das Ergebnis der Abfrage in ein Tabellenblatt, in das Datenmodell oder in beide geladen werden soll. Hier wählen Sie Nur Verbindung erstellen.

    Laden deaktivieren in Power Pivot

    Anders als in Power BI  sehen Sie in Power Query die Abfrage nicht kursiv dargestellt. In Excel sehen Sie das im Fenster Abfragen und Verbindungen.

    Laden deaktiviert in Excel

    Wozu benötigen wir deaktivierte Abfragen? Im obigen Beispiel Fall haben wir vier Tabellen:

    • Auftragspositionen
    • DimMaterial
    • DimKunden
    • FaktAuftragspositionen

    Die Tabelle Auftragspositionen dient uns nur als Quelle für die anderen Abfragen. Das Ergebnis dieser Abfrage benötigen wir nicht im Datenmodell. Durch Nur Verbindung erstellen erreichen wir genau das: Die Tabelle wird nicht geladen.

    Organisation mit Gruppen

    Mit den gezeigten Methoden erstellen Sie in Ihren Projekten mehr Abfragen. Einige davon werden nicht geladen. Andere sind Verweise. Damit Sie die Übersicht nicht verlieren, können Sie Abfragen entsprechend benennen. Ich benenne Abfragen meist mit einem Unterstrich als Präfix, wenn sie nicht geladen werden. Also beispielsweise _Auftragspositionen.

    Darüber hinaus können Sie Abfragen zusätzlich in Ordnern organisieren. In Power Query Gruppen genannt. Im der Liste der Abfragen finden Sie die Funktion Neue Gruppe. Legen Sie eine Gruppe an wird automatisch eine weitere mit Namen Andere Abfragen angelegt. In dieser anderen Gruppe legt Power Query neue, nicht zugeordnete Abfragen an. Via Drag and Drop können Sie die Abfragen organisieren:

    Gruppen in Power Query

    In Excel in Abfragen und Verbindungen wird diese Struktur entsprechend dargestellt:

    Gruppen in Abfragen und Verbindungen in Excel

    Kategorien: Artikel, Funktionen, Power BI, Power Pivot, Power Query, Tipps
    Holger Gubbels

    Holger Gubbels ist Spezialist für alles, was mit Business Intelligence zu tun hat. Der Diplom-Informatiker strukturiert und löst als Data Scientist und Berater der mogular GmbH tagtäglich komplexe Problemstellungen rund um die Datenanalyse. Als Dozent der Hochschule für Technik Stuttgart ist er ein ausgewiesener Experte seines Fachs und gewohnt, Inhalte einfach zu erläutern. Genau das macht er auch hier in seinen Beiträgen für das PowerPivotInsights Blog. Zusätzlich ist er Autor für die Computerwoche und den Springer-Verlag. Sie erreichen ihn unter hg@mogular.com

    Antwort schreiben

    Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.